CODEWITHSUNDEEP
sqlsqliteselect
charmixer
charmixer

Reputation: 651

SQLite AVG() with LEFT JOIN not returning all rows

I'm having a problem with a rating system.

When there is no matches in the LEFT JOIN it dosnt return the row at all when using AVG().

This is returning NULL if there is no results in join.

SELECT op_id,
       ...,
       rating_stars

But this removes the row.

SELECT op_id,
       ...,
       AVG(rating_stars)

This is my last attempt:

SELECT op_id,
       op_img,
       op_title,
       op_worktime,
       op_cookingtime,
       CASE rating_stars WHEN null THEN 0 ELSE AVG(rating_stars) END
FROM 
 opskrifter
LEFT JOIN 
  rating 
ON 
  rating.fk_op_id = opskrifter.op_id
WHERE 
  fk_type_id=1 
ORDER BY 
  op_id DESC

I need all the results and if it hasnt been rated yet it should just return 0 like this:

ID        | RATING
--- 1 --- | ---  0  ---
--- 2 --- | ---  4  ---
--- 3 --- | ---  0  ---

But the above SQL returns this:

ID        | RATING
--- 2 --- | ---  4  ---

I'm pretty sure I've done this in MySQL, with no problems.

Upvotes: 1

Views: 232

Answers (1)

John Woo
John Woo

Reputation: 263723

you need to have GROUP BY clause,

SELECT...
FROM...
WHERE...
GROUP BY op_id,
       op_img,
       op_title,
       op_worktime,
       op_cookingtime

Upvotes: 2

Related Questions