How to get the zero's for unsatisfied fields in group by clause in DB2

**FritzLeblanc** · Aug 24 '07, 01:08 PM

Originally posted by nagi28

Hi All,

I have pasted my DB2 Query and result below.

db2 => SELECT tp.COUNTRY, tp.KIND,count(t p.patent_key) as "COUNT OF BASICS" FROM PATENT AS tp, TRACKER AS tr WHERE (tp.PATENT_KEY= tr.PATENT_KEY) AND (tr.KEY in (SELECT key from TRACKER_DICT where STATE_NAME='Cre ate New Patent Record')) AND (tr.ARRIVED >='2004-06-22-00.00.00.000000 ' and tr.ARRIVED < '2008-06-23-00.00.00.000000 ') AND tp.basic_type=' B' GROUP BY tp.COUNTRY, tp.KIND

COUNTRY KIND COUNT OF BASICS
------------ --------- ---------------
CA A1 1
ID A2 1
MX A1 1
WO A1 1
WO A2 1

5 record(s) selected.

But i need to display zero for all the country and kind combinations which doesn't satisfy the conditions in the where clause.

Requirement is like below:
COUNTRY KIND COUNT OF BASICS
AU A1 0
CA A1 1
DK A1 0
GB A1 0
GB A2 0
ID A2 1
IN A1 0
JP A1 0
MX A1 1
MX B1 0
WO A1 1
WO A2 1
ZA A 0
ZW A1 0

Please help me in this.
Thanks in advance.

Assuming that you're missing some rows based on a non-match in the key columns, the following statement should provide the desired results.
SELECT tp.COUNTRY, tp.KIND,count(t p.patent_key) as "COUNT OF BASICS" FROM PATENT AS tp, left outer join TRACKER AS tr on tp.PATENT_KEY=t r.PATENT_KEY where (tr.KEY in (SELECT key from TRACKER_DICT where STATE_NAME='Cre ate New Patent Record')) AND (tr.ARRIVED >='2004-06-22-00.00.00.000000 ' and tr.ARRIVED < '2008-06-23-00.00.00.000000 ') AND tp.basic_type=' B' GROUP BY tp.COUNTRY, tp.KIND

Hope this helps,

Fritz

How to get the zero's for unsatisfied fields in group by clause in DB2

How to get the zero's for unsatisfied fields in group by clause in DB2

Comment