Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Update the Sortout file with record c... karthik_sripal SYNCSORT 8 Tue May 17, 2016 8:52 pm
No new posts CQM : Exec count is 0 but getpages &g... soundarr DB2 1 Thu Mar 17, 2016 10:08 pm
No new posts JCL SORT to split the file and put re... Bill Woodger DFSORT/ICETOOL 17 Wed Feb 17, 2016 4:17 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us