467,202 Members | 1,083 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,202 developers. It's quick & easy.

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

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
  • viewed: 1310
Share:
1 Reply
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.

Similar topics

3 posts views Thread by Robby McGehee | last post: by
4 posts views Thread by Steven T. Hatton | last post: by
2 posts views Thread by aj70000@hotmail.com | last post: by
3 posts views Thread by kattukuyil@hotmail.com | last post: by
1 post views Thread by florian.boldt@itgain.de | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.