Hi All,
I have pasted my DB2 Query and result below.
db2 => SELECT tp.COUNTRY, tp.KIND,count(tp.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='Create 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.