Coalesce/NULL (Count) Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Coalesce/NULL (Count) Help

Author

Topic

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-21 : 23:15:37

This code below gives me the following table:

ID       PROVIDER_CDE     GRF_COUNT   ACU_COUNT   
100034     GRF               4              1          
100054     GRF               3              1

SELECT Distinct
a.id, a.PROVIDER_CDE,  a.GRF_COUNT, E.ACU_COUNT       

FROM
(SELECT *
FROM
(SELECT id, PROVIDER_CDE, count(*) GRF_COUNT
FROM enrolment
WHERE Provider_cde = 'GRF'


GROUP BY id, PROVIDER_CDE))A

INNER JOIN
(SELECT *
FROM(
SELECT ID,PROVIDER_CDE, count(*) ACU_COUNT
FROM enrolment 
WHERE Provider_cde = 'ACU'
GROUP ID, PROVIDER_CDE))E
ON ID = ID
ORDER BY ID ASC

But it only gives me the ID where there is a count.
I need all the ID even if there is no count for example if i wanted to add another Column CLM_COUNT (below) all ID should be shown, not only the once where these is a count.

ID     PROVIDER_CDE   GRF COUNT   ACU_COUNT    CLM_COUNT
100034  GRF               4              1            0
100054  GRF               3              1            0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-21 : 23:23:33

do you have another table that store all the ID and PROVIDER_CDE ?

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-21 : 23:28:33

All data is taken from the same table (Enrolment).
No, I am only quering it. Not putting it into other tables.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-21 : 23:40:49

[code]
SELECT ID, PROVIDER_CDE,
COUNT(CASE WHEN GRF_COUNT = 'GRF' THEN 1 END) as GRF_COUNT,
COUNT(CASE WHEN GRF_COUNT = 'ACU' THEN 1 END) as ACU_COUNT,
COUNT(*) as CLM_COUNT
FROM enrolment
WHERE PROVIDER_CDE IN ('GRF', 'ACU')
GROUP BY ID, PROVIDER_CDE
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources