IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Count query to fetch count for two status


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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: 355
Location: New York

PostPosted: Wed Oct 21, 2009 11:21 am
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
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: 28
Location: germany

PostPosted: Wed Oct 21, 2009 11:59 am
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
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
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
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
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: 1281
Location: Belgium

PostPosted: Wed Oct 21, 2009 3:38 pm
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
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: 97
Location: India

PostPosted: Wed Oct 21, 2009 6:08 pm
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: 1281
Location: Belgium

PostPosted: Wed Oct 21, 2009 6:17 pm
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: 97
Location: India

PostPosted: Wed Oct 21, 2009 6:29 pm
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: 1281
Location: Belgium

PostPosted: Wed Oct 21, 2009 6:40 pm
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: 97
Location: India

PostPosted: Wed Oct 21, 2009 7:07 pm
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Need help with ADABAS query (COBOL-AD... All Other Mainframe Topics 0
No new posts CA7 count with DATE variable CA Products 0
No new posts How to turn off 'ACTION' SDSF output ... TSO/ISPF 2
No new posts To fetch records that has Ttamp value... DFSORT/ICETOOL 5
No new posts To get the count of rows for every 1 ... DB2 3
Search our Forums:

Back to Top