By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,418 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

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

P: 2
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.
Aug 20 '07 #1
Share this Question
Share on Google+
1 Reply


P: 12
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.
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(tp.patent_key) as "COUNT OF BASICS" FROM PATENT AS tp, left outer join TRACKER AS tr on tp.PATENT_KEY=tr.PATENT_KEY where (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

Hope this helps,

Fritz
Aug 24 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.