Summarizing Data

[ks01]

Hello ...

I have a query that extracts the following data:

  SELECT EFFECTIVE_DT,         
         PACKET_TYP,           
         COUNT(PACKET_TYP)     
    FROM RCX1.TPACKET_TEMP     
   WHERE PACKET_TYP = 'STA'    
GROUP BY EFFECTIVE_DT,         
         PACKET_TYP            
UNION ALL                      
  SELECT EFFECTIVE_DT,         
         PACKET_TYP,           
         COUNT(PACKET_TYP)     
    FROM RCX1.TPACKET_TEMP     
   WHERE PACKET_TYP = 'NON-STA'
GROUP BY EFFECTIVE_DT,         
         PACKET_TYP            
ORDER BY EFFECTIVE_DT DESC,         
         PACKET_TYP;

result set in a daily format:
[tt]
EFFECTIVE PACKET PACKET
DT TYP COUNT
---------- ------- -----------
2006-05-24 NON-STA 25
STA 17

2006-05-23 NON-STA 3
STA 12

2006-05-21 NON-STA 33
STA 34

2006-05-19 NON-STA 20
STA 12

2006-05-17 NON-STA 15 [/tt]

However, I want to get a summarized monthly "grouped" count like:[tt]

EFFECTIVE PACKET PACKET
DT TYP COUNT
---------- ------- -----------
2006-05 NON-STA 325
STA 417

2006-04 NON-STA 33
STA 412

2006-03 NON-STA 333
STA 434

2006-02 NON-STA 320
STA 412

2006-01 NON-STA 315 [/tt]

Can anyone provide me some suggestions?

Thanks in advance ...

 

[r937]

  SELECT extract(year from EFFECTIVE_DT),
         extract(month from EFFECTIVE_DT),
         'NON-STA' as PACKET_TYP,
         COUNT(PACKET_TYP)
    FROM RCX1.TPACKET_TEMP
   WHERE PACKET_TYP = 'NON-STA'
GROUP BY extract(year from EFFECTIVE_DT),
         extract(month from EFFECTIVE_DT)
UNION ALL
  SELECT extract(year from EFFECTIVE_DT),
         extract(month from EFFECTIVE_DT),
         'STA',
         COUNT(PACKET_TYP)
    FROM RCX1.TPACKET_TEMP
   WHERE PACKET_TYP = 'STA'
GROUP BY extract(year from EFFECTIVE_DT),
         extract(month from EFFECTIVE_DT)
ORDER BY extract(year from EFFECTIVE_DT) desc,
         extract(month from EFFECTIVE_DT) desc

disclaimer: i'm guessing at the ANSI EXTRACT() function

to make this work in whatever database you're running (which you neglected to mention, although in the ANSI SQL forum, this shouldn't matter), replace EXTRACT() with whatever function your database supports

r937.com | rudy.ca

 

[ks01]

Sorry, I neglected to mention the DBMS. I'm using DB2 for z/OS (mainframe).