Working With NULL and 0 Values (I think?) 1

[DavidC271]

I am trying to pull a query that will show me all my branches and how many reports they have open at any given time. I want to see 0 if they do not have any reports open. Here is my SQL:

SELECT DISTINCT branch_id, count(report_id) AS rptCount
FROM report_feeder
WHERE rpttype = 'report' AND report_status = 'open'
GROUP BY branch_id
ORDER BY branch_id;

+-----------+----------+
| branch_id + rptCount |
------------+-----------
|DIR        +         1|
|EB1        +         1|
|EB2        +        18|
------------+-----------

What I would like to see are those branches that have 0 open records also.
Can anyone help with the SQL? I have tried may suggestions from the MySQL reference guide such as IFNULL, IS NULL and COALESCE.
Am I really dealing with NULL here or a 0 record count?

Any help would be outstanding!

Thanks,
David

 

[feherke]

Hi

I would do it like this :

[b]select[/b]
branch_id[teal],[/teal]
count[teal]([/teal]report_id[teal])[/teal] [b]as[/b] rptcount[teal],[/teal]
count[teal]([/teal]case report_status when [green][i]'open'[/i][/green] then [purple]1[/purple] end[teal])[/teal] [b]as[/b] opencount[teal],[/teal]
count[teal]([/teal]nullif[teal]([/teal]report_status[teal],[/teal] [green][i]'open'[/i][/green][teal]))[/teal] [b]as[/b] notopencount

[b]from[/b] report_feeder

[b]where[/b] rpttype [teal]=[/teal] [green][i]'report'[/i][/green]

[b]group[/b] [b]by[/b] branch_id

[b]order[/b] [b]by[/b] branch_id

Feherke.

http://free.rootshell.be/~feherke/

 

[DavidC271]

Feherke,
Thank you very much for your answer. I took what you gave me and adjusted a bit to come up with what I was looking for.

FYI - case is the one method I did not try...

SELECT branch_id, COUNT(CASE report_status WHEN 'open' THEN 1 END) AS rptCount
FROM report_feeder
WHERE rpttype = 'report'
GROUP BY branch_id
ORDER BY branchid;

That gave me my two column output with all branches showing their open reports status including those with 0 counts.

Thanks again for your help,
David