Count query to fetch count for two status

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

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?

arvind.m · Posted: Wed Oct 21, 2009 11:07 am

use OR in the WHERE clause. i think you'll get count for both the Status_cd.

ksk · Posted: Wed Oct 21, 2009 11:21 am

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

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

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

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'

bauer · New User Joined: 03 Mar 2009 Posts: 28 Location: germany

ajeshrn,

try this:

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

Hi bauer,

Thanks for the reply.
If I can get some query which can improve the performance, thn it would be great

. 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.

Steve Davies · New User Joined: 15 Oct 2009 Posts: 32 Location: UK

Try

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

Steve Davies · New User Joined: 15 Oct 2009 Posts: 32 Location: UK

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....

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

Thanks Steve!!! I will try this.

GuyC · Posted: Wed Oct 21, 2009 3:38 pm

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

Thanks a lot GuyC.Will try it out.

rajesh_mbt · New User Joined: 27 Mar 2006 Posts: 97 Location: India

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

GuyC · Posted: Wed Oct 21, 2009 6:17 pm

sql just above does NOT work? It doesn't even prepare

I guess testing is part of the ocean

rajesh_mbt · New User Joined: 27 Mar 2006 Posts: 97 Location: India

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.

GuyC · Posted: Wed Oct 21, 2009 6:40 pm

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

rajesh_mbt · New User Joined: 27 Mar 2006 Posts: 97 Location: India

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!!!

bhairon singh rathore · New User Joined: 19 Jun 2008 Posts: 91 Location: banglore

Hi Rajesh,
Please try your query before posting

thanks