Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Getting the count using SQL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Nisha sam

New User


Joined: 18 Jan 2008
Posts: 11
Location: India

PostPosted: Tue Jun 03, 2008 12:19 pm    Post subject: Getting the count using SQL
Reply with quote

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
View user's profile Send private message

chandu.be

New User


Joined: 17 Jul 2006
Posts: 9
Location: Bagalore

PostPosted: Tue Jun 03, 2008 12:57 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nisha sam

New User


Joined: 18 Jan 2008
Posts: 11
Location: India

PostPosted: Tue Jun 03, 2008 2:03 pm    Post subject: Reply to: Getting the count
Reply with quote

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
View user's profile Send private message
Nisha sam

New User


Joined: 18 Jan 2008
Posts: 11
Location: India

PostPosted: Tue Jun 03, 2008 3:44 pm    Post subject: Reply to: Getting the count using SQL
Reply with quote

Please people...can any one help me with this one...
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jun 03, 2008 3:50 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nisha sam

New User


Joined: 18 Jan 2008
Posts: 11
Location: India

PostPosted: Tue Jun 03, 2008 4:29 pm    Post subject: Reply to: Getting the count using SQL
Reply with quote

Ashimer,
ROLLUP is not working with mine too.
Is there any equivalent commands in the lower versions?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jun 03, 2008 4:43 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jun 03, 2008 4:52 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nisha sam

New User


Joined: 18 Jan 2008
Posts: 11
Location: India

PostPosted: Wed Jun 04, 2008 12:00 pm    Post subject: Reply to: Getting the count using SQL
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts SORT Trailer Count - LRECL Output co... amorante DFSORT/ICETOOL 5 Tue Aug 29, 2017 8:57 pm
No new posts CICS Transaction attach count of supp... lind sh CICS 1 Wed Jun 21, 2017 1:33 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us