SQL count on several elements that might not be there 1

[lhg1]

Hi

I need to do an SQL that count the number of elements in a table, and if it isn't there it shoul say 0

ex on the sql I using now

SELECT   phd_id, count(*) Count
FROM     q3
WHERE    dvc_trx_sts_cd = 'PE'
and phd_id in ('ABMSR01','KDHLR01','KDHLR02')
GROUP BY phd_id;

the result is

phd_id count
ABMSR01 1433

And should be

phd_id count
ABMSR01 1433
KDHLR01 0
KDHLR02 0

Hope someone can help

/LHG

 

[PHV]

Provided you have a table containing all the possible phd:
SELECT phd_id
,(SELECT COUNT(*) FROM q3 WHERE dvc_trx_sts_cd='PE' AND phd_id=A.phd_id) Count
FROM your_phd_table A
WHERE phd_id IN ('ABMSR01','KDHLR01','KDHLR02')
GROUP BY phd_id;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lhg1]

Thanks PHV

Your solution worked perfect.

/Lhg