Geek asks and answers

Inadmissible, because it is not contained in an aggregate function or a group by a sentence

I would like to create a query that receives a product from the product table, its type and category from the type table, and the number of songs on the product. But for some reason, this request causes an error. It started when I addedcount(n.name)

SELECT p.name, p.publisher, p.description, p.price, p.picture
     , p.releasedate, t.type, t.category, count(n.name) AS songs
  FROM Products p
 INNER JOIN ProductType t ON (p.type_id = t.id)
 INNER JOIN Songs n ON (p.id = n.product_id)

The error I get is

The column "Products.name" is not valid in the selection list because it is not contained in the aggregate function or in the GROUP BY clause.

+5
source share
2 answers

Group only rows Songs, and then attach the aggregated data instead of the table itself Songs:

SELECT p.name, p.publisher, p.description, p.price, p.picture
     , p.releasedate, t.type, t.category, n.songs
  FROM Products p
 INNER JOIN ProductType t ON (p.type_id = t.id)
 INNER JOIN (
   SELECT
     product_id,
     COUNT(n.name) AS songs
   FROM Songs
   GROUP BY product_id
 ) n ON (p.id = n.product_id)

, GROUP BY, , .

+6
SELECT p.name, p.publisher, p.description, p.price, p.picture, p.releasedate, t.type, t.category, count(1) AS songs
                    FROM Products p
                    INNER JOIN ProductType t ON (p.type_id = t.id)
                    INNER JOIN Songs n ON (p.id = n.product_id)
GROUP BY p.name, p.publisher, p.description, p.price, p.picture, p.releasedate, t.type, t.category
0

More articles:


All Articles