Expression (`L`.`product_id`) must depend only on group keys or aggregates

SQL++
1

Hello,

Why I get this error message and how can I fix it ?

SELECT 
P.product_id as PID,

L.product_id as LikedPID,
COUNT(L.likedAt) as likes
FROM  products P 

INNER JOIN products_likes L
ON P.product_id = L.product_id
WHERE  P.product_id='3221132' GROUP BY P.product_id;
2

You will have to group by all selected columns that are not the aggregates - in other words, try:

SELECT 
P.product_id as PID,

L.product_id as LikedPID,
COUNT(L.likedAt) as likes
FROM  products P 

INNER JOIN products_likes L
ON P.product_id = L.product_id
WHERE  P.product_id='3221132' GROUP BY P.product_id, L.product_id;

HTH.

1 Like
3 
SELECT 
P.product_id as PID,
COUNT(L.likedAt) AS likes
FROM  products P 
INNER JOIN products_likes L ON P.product_id = L.product_id
WHERE  P.product_id='3221132' 
GROUP BY P.product_id;
  1. Your ON clause has equality PID, LikedPID are same.
  2. Projection of Aggregate queries can only have group by expressions, aggregates or expressions (nested aggregates not allowed) on group by expressions, aggregates .
  3. As Aggregate queries collapses results you can’t project arbitrary field. If such information needed use ARRAY_AGG()