CODEWITHSUNDEEP
mysql
3ND
3ND

Reputation: 430

MySQL query count returning wrong value

So, this query returns result 1 even if there is no tasks related to list. How to fix it so it returns 0 for lists with zero tasks?

SELECT 
  l.list_id, l.list_name, l.list_time_created, 
  COUNT(CASE WHEN t.task_id IS NULL THEN 0 ELSE 1 END) AS numberOfTasks,
  SUM(CASE WHEN t.task_completed IS NULL THEN 0 ELSE t.task_completed END) AS numberOfCompletedTasks
FROM list l
LEFT JOIN task t ON l.list_id = t.list_id 
WHERE l.user_id = ':user_id'
GROUP BY l.list_id;

Upvotes: 0

Views: 491

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If there are no tasks, then the rows will be filtered out. You want a left join:

SELECT l.list_id, l.list_name, l.list_time_created,
       COUNT(t.task_id ) AS numberofTasks,
       SUM(t.task_completed = 1) AS numberofCompletedTasks
FROM list l LEFT JOIN
     task t
     ON l.list_id = t.list_id 
WHERE l.user_id = ':user_id'
GROUP BY l.list_id;

I introduced table aliases. These make a query easier to write and to read.

I also simplified the logic for numberofCompletedTasks. Your original formulation is fine (and even standard SQL), but MySQL has a nice short-cut.

Upvotes: 1

Related Questions