| View previous topic :: View next topic |
| Author |
Message |
srini_igsi
Beginner
Joined: 12 May 2006
Posts: 41
Topics: 16
Location: Mumbai
|
 Posted: Thu Mar 19, 2009 2:39 pm Post subject: Retrieving the count with COUNT(*) and GROUP BY |
 |
|
Could some body please let me know if there is a way to get the consolidated count for all the groups together instead of one per group.
| Code: |
SELECT COUNT(*)
FROM DB2T.SOSRSTK_REL C
INNER JOIN DB2T.SOSRSTK_REL E
ON ( C.CORP = '05G'
AND C.RELEASE_DATE <= '2009-03-06'
AND C.RELEASE_ID = E.RELEASE_ID )
INNER JOIN DB2T.SOSRSTK_REL S
ON S.RELEASE_ID = C.RELEASE_ID
AND S.REL_TYPE IN ('1', '2', '3', '4')
AND S.REL_STATUS IN ('C', 'O', 'P', 'H', 'R')
GROUP BY CORP
,C.RELEASE_DATE
,C.RELEASE_ID
,C.RT_IND
,C.PROD_CODE
,SUM_TOT_SHRS |
_________________
Thanks,
-Srini |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 19, 2009 3:11 pm Post subject: |
|
|
srini_igsi,
Just remove the GROUP BY statement |
|
| Back to top |
|
|
srini_igsi
Beginner
Joined: 12 May 2006
Posts: 41
Topics: 16
Location: Mumbai
|
| Posted: Fri Mar 20, 2009 7:45 am Post subject: |
|
|
Sorry Kolusu, I need the count of all the groups together after applying GROUP BY.
_________________
Thanks,
-Srini |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Fri Mar 20, 2009 12:57 pm Post subject: |
|
|
srini_igsi,
You need to understand how the the group by works
consider this is your table
| Code: |
COL1
====
A
A
A
B
B
C
|
| Code: |
SELECT COUNT(*)
FROM TABLE
GROUP BY COL1 |
Will produce
Now the total sum of the counts is 6. remove the Group by clause and see the
| Code: |
SELECT Count(*)
FROM TABLE
|
will produce
Do you want to produce 3 or as there are 3 different unique values or do you want the total of all them? If you want 3 then you can use this sql (untested)
| Code: |
SELECT COUNT(Z.GRPCOUNT)
FROM (SELECT COUNT(*) AS GRPCOUNT
FROM DB2T.SOSRSTK_REL C
INNER JOIN DB2T.SOSRSTK_REL E
ON (C.CORP = '05G'
AND C.RELEASE_DATE <= '2009-03-06'
AND C.RELEASE_ID = E.RELEASE_ID )
INNER JOIN DB2T.SOSRSTK_REL S
ON S.RELEASE_ID = C.RELEASE_ID
AND S.REL_TYPE IN ('1', '2', '3', '4')
AND S.REL_STATUS IN ('C', 'O', 'P', 'H', 'R')
GROUP BY CORP
,C.RELEASE_DATE
,C.RELEASE_ID
,C.RT_IND
,C.PROD_CODE
,SUM_TOT_SHRS) Z
; |
|
|
| Back to top |
|
|
srini_igsi
Beginner
Joined: 12 May 2006
Posts: 41
Topics: 16
Location: Mumbai
|
| Posted: Sat Mar 21, 2009 7:31 am Post subject: |
|
|
Thank you so much Kolusu. I know how GROUP BY works but I was looking out for the tot of three of them only. I will run this query and let you know the result on Monday.
_________________
Thanks,
-Srini |
|
| Back to top |
|
|
srini_igsi
Beginner
Joined: 12 May 2006
Posts: 41
Topics: 16
Location: Mumbai
|
| Posted: Mon Mar 23, 2009 11:12 am Post subject: |
|
|
Thank you so much Kolusu. It's perfectly working. This is the reason why I always trust MVSFORUMS website.
It has made my program logic very simple.
_________________
Thanks,
-Srini |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
