Posted: Tue Sep 21, 2004 10:41 pm Post subject: how to retrieve tot count in COUNT col when mo & ye are
My query is as
Code:
SELECT ITSSADLP_ID_RACF, ITSSADLP_REG_TAB
, COUNT (ITSSADLP_ID_RACF)
, YEAR (ITSSADLP_STAT_TS)
, MONTH (ITSSADLP_STAT_TS)
FROM SCITST.ITSSADLP
WHERE ITSSADLP_ID_RACF = 'S027059'
GROUP BY ITSSADLP_ID_RACF, ITSSADLP_REG_TAB, ITSSADLP_STAT_TS
ORDER BY ITSSADLP_ID_RACF, ITSSADLP_REG_TAB
the result is as
Code:
ITSSADLP ITSSADLP
ID REG
RACF TAB COL1 COL2 COL3
-------- -------- ----------- ----------- -----------
D027059 ST 6 2004 4
D027059 ST 5 2004 4
D027059 ST 5 2004 5
D027059 ST 6 2004 5
D027059 ST 4 2004 6
how I need is as
Code:
ITSSADLP ITSSADLP
ID REG
RACF TAB COL1 COL2 COL3
-------- -------- ----------- ----------- -----------
D027059 ST 11 2004 4
D027059 ST 11 2004 5
D027059 ST 4 2004 6
i.e when month and year matchs, I need to have the count of RACF occurences. Can you help me in fine tuning the query.
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Wed Sep 22, 2004 8:51 am Post subject:
Satya_reddy,
Try this sql
Code:
SELECT C.ID
,C.REG
,SUM(C.CNT)
,C.YR
,C.MN
FROM (SELECT ITSSADLP_ID_RACF AS ID
,ITSSADLP_REG_TAB AS REG
,COUNT (ITSSADLP_ID_RACF) AS CNT
,YEAR (ITSSADLP_STAT_TS) AS YR
,MONTH (ITSSADLP_STAT_TS) AS MN
FROM SCITST.ITSSADLP
WHERE ITSSADLP_ID_RACF = 'S027059'
GROUP BY ITSSADLP_ID_RACF, ITSSADLP_REG_TAB, ITSSADLP_STAT_TS ) C
GROUP BY C.ID
,C.REG
,C.YR
,C.MN
ORDER BY C.ID
,C.REG
;
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
