|
View previous topic :: View next topic
|
| Author |
Message |
Nisha sam
New User
Joined: 18 Jan 2008
Posts: 11
Location: India
|
|
|
|
Hi,
I have to get the counts like the following
| Code: |
PROCESS-CTR HEARING-REP-TYP HEARING-TYPE COUNT
----------- --------------- ------------ ---------
1 A P 78
1 A 78
1 C C 43
1 C P 48
1 C 91
1 I C 42
1 I P 28
1 I 70
1 P C 22
1 P P 46
1 P 68
1 X C 1
1 X P 3
1 X 4
1 311
5 C C 12
5 C P 6
5 C 18
5 I C 2
5 I P 1
5 I 3
5 P C 2
5 P P 8
5 P 10
5 31
|
That is the counts for
combination of COL1 and COL2 and COL3
combination of COL1 and COL2
Counts for COL1
When i give the following query I could get only the counts for the combination of COL1,COL2 and COL3.
SELECT COUNT(*),PROCESS_CTR,HEARING_REP_TYP,HEARING_TYPE from table1
group by PROCESS_CTR,HEARING_REP_TYP,HEARING_TYPE
order by PROCESS_CTR,HEARING_REP_TYP,HEARING_TYPE;
Please help me to resolve it.
Thanks,
Nisha[/code] |
|
| Back to top |
|
 |
chandu.be
New User
Joined: 17 Jul 2006
Posts: 9
Location: Bagalore
|
|
|
|
Hi Nisha,
I have tried the same query in my shop. It is woring fine for me.
Can you provide us more information on what exactly the problem that you are facing with this query.
Regards,
Chandu |
|
| Back to top |
|
|
Nisha sam
New User
Joined: 18 Jan 2008
Posts: 11
Location: India
|
|
|
|
Hi,
the problem is that only the count for combination of COL1,COL2 and COL3 are obtained with this query.
But i also want the count for combination of COL1 and COL2(given as AA) and also Count for COL1 (given as BB)alone.
| Code: |
PROCESS-CTR HEARING-REP-TYP HEARING-TYPE COUNT
----------- --------------- ------------ ---------
1 A P 78
1 A 78------> AA
1 C C 43
1 C P 48
1 C 91------> AA
1 I C 42
1 I P 28
1 I 70------> AA
1 P C 22
1 P P 46
1 P 68------> AA
1 X C 1
1 X P 3
1 X 4
1 311------> BB
|
|
|
| Back to top |
|
|
Nisha sam
New User
Joined: 18 Jan 2008
Posts: 11
Location: India
|
|
|
|
| Please people...can any one help me with this one... |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Nisha,
from db2 version 8.1 onwards there is a group by function ROLL UP
GROUP BY ROLLUP(A,B,C) which is same as
GROUP BY GROUPING SETS((A,B,C)
,(A,B)
,(A)
for more usage on this refer to DB2 v81. sql cookbook ...
this function if it works at ur shop then u should be thru with ur requirement ..
i am not able to run this at my shop .... do try and let me know mean while let me try other alternatives .... |
|
| Back to top |
|
|
Nisha sam
New User
Joined: 18 Jan 2008
Posts: 11
Location: India
|
|
|
|
Ashimer,
ROLLUP is not working with mine too.
Is there any equivalent commands in the lower versions? |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Nisha,
try this one
| Code: |
(SELECT A,B,C,COUNT(*) FROM
SESSION.EMP
GROUP BY A,B,C)
UNION ALL
(SELECT A,B,'' AS C,COUNT(*) FROM
SESSION.EMP
GROUP BY A,B)
UNION ALL
(SELECT A,'' AS B,'' AS C,COUNT(*) FROM
SESSION.EMP
GROUP BY A);
|
change A,B,C by PROCESS_CTR,HEARING_REP_TYP,HEARING_TYPE and session.emp by your table name ...
try and let me know ... |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
My data is
| Code: |
PROCESS-CTR HEARING-REP-TYP HEARING-TYPE
----------- --------------- ------------
1 A P
1 A P
1 A C
1 C C
|
and output after running the query was
| Code: |
---------+---------+-
A B C COUNT
---------+---------+-
1 A C 1
1 A P 2
1 C C 1
1 A 3
1 C 1
1 4
|
I hope this is what you are expecting ... |
|
| Back to top |
|
|
Nisha sam
New User
Joined: 18 Jan 2008
Posts: 11
Location: India
|
|
|
|
Hi Ashimer,
Thanks a lot...its working perfectly fine..
I also came up with another query with SUM but it was very complex.
This is really straight forward.Thank you onec again. |
|
| Back to top |
|
|
|
|
