DB2 Count Report

IBM's flagship relational database management system

DB2 Count Report

Postby gopurs » Fri Sep 09, 2016 10:40 am

Table 1

Date        Code  Key
2016-08-01  D1234 KEY1
2016-08-02  D1214 KEY2
2016-08-01  D1214 KEY3
2016-08-01  P1234 KEY4
2016-08-01  P1234 KEY5

Table 2

code  key  line
D1234 KEY1 LN1
D1214 KEY2 LN1
D1214 KEY3 LN1
D4444 KEY4 LN1
D1234 KEY4 LN2
D2222 KEY4 LN3
D2343 KEY4 LN4
D2324 KEY4 LN5
D1234 KEY5 LN1

Above are the two table i have and I need to find the top 5 codes for the day. Will need to count from table 2 only if the code(first byte) in table1 is not 'D'. Join field for the two tables is the key field.

Count Output

2016-08-01      D1234  3 (1 from table2)                                                           2016-08-02          D1224  3                    
                     D1214  2 (won't count from table 2)                                                         D1334  2
                      D4444  1 (only the first one for the key will be taken for that key)                          D4444  2
                                                                                                                  D4434  1
                                                                                                          D9994  1

The count may be different for different days and the code may be different

Can I do this using SQL? I don't want to do it using temporary table as our Business objects dont support temporary table and so i was trying to see if there is any way to do this using SQL.

Any help or guidance is helpful.

Posts: 1
Joined: Fri Sep 09, 2016 10:30 am
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Count Report


Re: DB2 Count Report

Postby NicC » Fri Sep 09, 2016 1:37 pm

Please use the code tags when posting data, code or anything requiring spacing to be maintained. Your column headings did not line up with your column data and your report was all scrunched up. This request is made so often that searching the forum, or even just poking around it, will give you the information needed.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Global moderator
Posts: 2670
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 101 times

Return to DB2


  • Related topics
    Last post