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
 

 

Count query to fetch count for two status

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

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Wed Oct 21, 2009 10:27 am    Post subject: Count query to fetch count for two status
Reply with quote

Hi ,

I have two separate count (*) query, to fetch count for two status codes:

Count A

Select Count(*)
From Table1
Where Status_cd = 'A'

Count B

Select Count(*)
From Table1
Where Status_cd = 'B'.

The Where condition are same, the only difference is the status code changes. IS there a way I can club these two queries?
Back to top
View user's profile Send private message

arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Wed Oct 21, 2009 11:07 am    Post subject:
Reply with quote

use OR in the WHERE clause. i think you'll get count for both the Status_cd.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Wed Oct 21, 2009 11:21 am    Post subject:
Reply with quote

Yes, using OR in the WHERE clause you can achieve.

Select Count(*)
From Table1
Where Status_cd = 'A' or Status_cd = 'B';
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Wed Oct 21, 2009 11:29 am    Post subject:
Reply with quote

Hi,

The expected result is as below.

I need a single query which gets count for both cases seperately, in this case we will get the count value stored in WS-COUNT-A variable , similarily I need value for B to be captured in the same query to a different variable WS-COUNT-B.Basically I need to combine both the queries, but the count value of A should be in WS-COUNT-A and that of B should be in WS-COUNT-B.Is this possible?

Select Count(*)
into :WS-COUNT-A
From Table1
Where Status_cd = 'A'
Back to top
View user's profile Send private message
bauer

New User


Joined: 03 Mar 2009
Posts: 26
Location: germany

PostPosted: Wed Oct 21, 2009 11:59 am    Post subject:
Reply with quote

ajeshrn,

try this:

Code:


select    COUNT_A
          , COUNT_B

from    (
        select count(*) AS COUNT_A
           from table1 where ...
        ) AS SEL1
      , (
        select count(*) AS COUNT_B
           from table1 where ...
        ) AS SEL2




Remark: No advantage for performance. Same as two single selects regarding performance.

bauer
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Wed Oct 21, 2009 12:11 pm    Post subject:
Reply with quote

Hi bauer,

Thanks for the reply.
If I can get some query which can improve the performance, thn it would be great icon_confused.gif . As of now,we are having seven status codes as of now, and in the existing program we have seven seperate count(*) queries and a count(*) query to find the total count,which is done by giving status_cd like '%'.So because of all these 7 individual and 1 total count(*), I feel my program will have some performance issue, if the number of records in the table is very huge. icon_rolleyes.gif
Back to top
View user's profile Send private message
Steve Davies

New User


Joined: 15 Oct 2009
Posts: 32
Location: UK

PostPosted: Wed Oct 21, 2009 2:09 pm    Post subject:
Reply with quote

Try

Select Status_cd, Count(*)
From Table1
Where Status_cd in ( 'A', 'B', 'C', 'D' etc.....)
group by Status_cd ;
Back to top
View user's profile Send private message
Steve Davies

New User


Joined: 15 Oct 2009
Posts: 32
Location: UK

PostPosted: Wed Oct 21, 2009 2:44 pm    Post subject:
Reply with quote

Select Status_cd, Count(*)
From Table1
Where Status_cd in ( 'A', 'B', 'C', 'D' etc.....)
group by Status_cd ;

.........perhaps I should have said to put the above SQL in a cursor, selecting into host variable fields, and have your program fetch each row and you can keep a tally of all the counts, giving you the total number....
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Wed Oct 21, 2009 2:46 pm    Post subject:
Reply with quote

Thanks Steve!!! I will try this.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Oct 21, 2009 3:38 pm    Post subject:
Reply with quote

Code:
Select Count(*)
,sum(case when status_cd = 'A' then 1 else 0 end) as cntA
,sum(case when status_cd = 'B' then 1 else 0 end) as cntB
,sum(case when status_cd = 'C' then 1 else 0 end) as cntC
From Table1
Where Status_cd in ( 'A', 'B', 'C')


should give you the answer in one sql
should only be max one TB-scan (or IX-scan)
and no sorts needed
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Wed Oct 21, 2009 3:46 pm    Post subject:
Reply with quote

Thanks a lot GuyC.Will try it out.
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Wed Oct 21, 2009 6:08 pm    Post subject:
Reply with quote

Hi,
Hope, this would be another better way to solve your query. Please try it and confirm us.

Code:

 SELECT
     CASE
         WHEN STATUS_CD = 'A'
             THEN COUNT(*)
     END
    , 
     
  CASE
         WHEN STATUS_CD = 'B'
             THEN COUNT(*)
     END

FROM TABLE1
WHERE STATUS_CD IN ('A','B')



While receiving into host variables use two variables like :HOST-A and :HOST-B to receive the each counts.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Oct 21, 2009 6:17 pm    Post subject:
Reply with quote

sql just above does NOT work? It doesn't even prepare icon_rolleyes.gif
I guess testing is part of the ocean
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Wed Oct 21, 2009 6:29 pm    Post subject:
Reply with quote

Hi GuyC,
I believe it should be working fine for any DB2 compiler. I did not check it at my end but I had experiences in similar set of queries.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Oct 21, 2009 6:40 pm    Post subject:
Reply with quote

Seriously ? The SQL is grammatically wrong so you will get a error with any decent DB2 precompiler.

Code:
SELECT CASE WHEN creator = 'SYSIBM' THEN COUNT(*) END
    ,  CASE WHEN creator = 'DB2OSC' THEN COUNT(*) END
FROM SYSIBM.SYSTABLES
WHERE creator IN ('SYSIBM','DB2OSC')
A SELECT STATEMENT WITH NO GROUP BY CLAUSE CONTAINS A COLUMN NAME AND AN AGGREGATE FUNCTION IN THE SELECT CLAUSE OR A COLUMN NAME IS CONTAINED IN THE SELECT CLAUSE BUT NOT IN THE GROUP BY CLAUSE. SQLCODE=-122, SQLSTATE=42803
Back to top
View user's profile Send private message
rajesh_mbt

New User


Joined: 27 Mar 2006
Posts: 95
Location: India

PostPosted: Wed Oct 21, 2009 7:07 pm    Post subject:
Reply with quote

I apology for that post if is caused some confusions :-(. I checked at my end and facing same error. Let me try to correct the query and update you if possible!!!
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Thu Oct 22, 2009 12:01 pm    Post subject:
Reply with quote

Hi Rajesh,
Please try your query before posting

thanks
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
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


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