View previous topic :: View next topic
|
Author |
Message |
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? |
|
Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008 Posts: 205 Location: Hyderabad
|
|
|
|
use OR in the WHERE clause. i think you'll get count for both the Status_cd. |
|
Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006 Posts: 355 Location: New York
|
|
|
|
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 |
|
|
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' |
|
Back to top |
|
|
bauer
New User
Joined: 03 Mar 2009 Posts: 28 Location: germany
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
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 ; |
|
Back to top |
|
|
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.... |
|
Back to top |
|
|
ajeshrn
New User
Joined: 25 Mar 2009 Posts: 78 Location: India
|
|
|
|
Thanks Steve!!! I will try this. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
ajeshrn
New User
Joined: 25 Mar 2009 Posts: 78 Location: India
|
|
|
|
Thanks a lot GuyC.Will try it out. |
|
Back to top |
|
|
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.
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
sql just above does NOT work? It doesn't even prepare
I guess testing is part of the ocean |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
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!!! |
|
Back to top |
|
|
bhairon singh rathore
New User
Joined: 19 Jun 2008 Posts: 91 Location: banglore
|
|
|
|
Hi Rajesh,
Please try your query before posting
thanks |
|
Back to top |
|
|
|