CODEWITHSUNDEEP
sqlgroup-bynull
iposave
iposave

Reputation: 97

Show null values in group by query as zero

I have two tables, tasks and status. Tasks table have columns task_id, project_id task_content and status_id. Status table have columns status_id and status_name. I want to get query which takes all status names and counts how much are there tasks with that status names. My query looks like this:

EDIT: i forgot to add project_id. Query needs to check only tasks within one project.

SELECT s.status_name, COUNT(t.status_id) AS tasks
FROM status s
LEFT JOIN tasks t
ON s.status_id = t.status_id
WHERE t.project_id = 1
GROUP BY s.status_name

My query is working good, but when there are no tasks with one of statuses this status is left out from query, i want it to show zero like: 

    status name | tasks 
     status 1   |  3 
     status 2   |  2
     status 3   |  0

but my query looks like this: 

status name | tasks 
 status 1   |  3 
 status 2   |  2

Upvotes: 3

Views: 4462

Answers (5)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

THIS ANSWERS THE QUESTION BASED ON ORIGINAL POST (BEFORE ANY EDIT MADE) :

Use column_name (i.e. t.status_id) inside count() :

SELECT s.status_name, COUNT(t.status_id) AS tasks
FROM status s LEFT JOIN 
     tasks t
     ON s.status_id = t.status_id
GROUP BY s.status_name;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Just move the where condition to the on clause:

SELECT s.status_name, COUNT(t.status_id) AS tasks
FROM status s LEFT JOIN
     tasks t
     ON s.status_id = t.status_id AND
        t.project_id = 1
GROUP BY s.status_name;

COUNT() never returns a NULL value, so you don't need any extra check. It returns 0 if there are no matches.

Upvotes: 0

iposave
iposave

Reputation: 97

Thanks everyone for help, i found solution. I will post it for everyone who will have same or similar problem in future. Using 'WHERE' mess up my left join so there is workaround: 

SELECT s.status_name, (IFNULL(COUNT(t.task_id), 0)) AS tasks
FROM status s 
LEFT OUTER JOIN tasks t
ON  s.status_id = t.status_id 
AND t.project_id = 1 
GROUP BY s.status_name

Upvotes: 4

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

try like below

SELECT s.status_name, COUNT(t.status_id) AS tasks
FROM status s
LEFT JOIN tasks t
ON s.status_id = t.status_id
GROUP BY s.status_name

Note you need status 3 this values in your left table also then it will comes to the name other wise it will not

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Try below- use count(t.status_id) instead of count(*)

SELECT s.status_id,s.status_name, COUNT(t.status_id) AS tasks
    FROM status s
    LEFT JOIN tasks t
    ON s.status_id = t.status_id
    GROUP BY s.status_id,s.status_name

Upvotes: 0

Related Questions