469,271 Members | 1,690 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Optimized statement for subquery using count(*)

Hello,

DB2 LUW V8 FixPack 13.

create table Table (ID varchar(20), USED char)

I need to find out the total row count per ID, as well as the row count
where USED=Y.

I could do this with a simple:

select X.ID, X.Total, Y.Total from
(select ID, COUNT(*) as Total from Table group by ID) as X
(select ID, COUNT(*) as Used_Total from Table where USED='Y' group by
ID) as Y
where X.ID=Y.ID

Depending on my table size, this query can be resource intensive and
take some time to finish.

Is there any other way to achieve the same result with an optimized
statement?

Thanks in advance, Michel

Nov 21 '06 #1
4 5371
Michel Esber wrote:
Hello,

DB2 LUW V8 FixPack 13.

create table Table (ID varchar(20), USED char)

I need to find out the total row count per ID, as well as the row count
where USED=Y.

I could do this with a simple:

select X.ID, X.Total, Y.Total from
(select ID, COUNT(*) as Total from Table group by ID) as X
(select ID, COUNT(*) as Used_Total from Table where USED='Y' group by
ID) as Y
where X.ID=Y.ID

Depending on my table size, this query can be resource intensive and
take some time to finish.

Is there any other way to achieve the same result with an optimized
statement?

Thanks in advance, Michel
Are you trying to find the ID's for which the exists a USED <'Y'?
SELECT DISTINCT ID FROM TABLE WHERE USED <'Y'
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 21 '06 #2
Michel Esber wrote:
Hello,

DB2 LUW V8 FixPack 13.

create table Table (ID varchar(20), USED char)

I need to find out the total row count per ID, as well as the row count
where USED=Y.

I could do this with a simple:

select X.ID, X.Total, Y.Total from
(select ID, COUNT(*) as Total from Table group by ID) as X
(select ID, COUNT(*) as Used_Total from Table where USED='Y' group by
ID) as Y
where X.ID=Y.ID

Depending on my table size, this query can be resource intensive and
take some time to finish.

Is there any other way to achieve the same result with an optimized
statement?
SELECT id, COUNT(*) AS total,
SUM(CASE WHEN used = 'Y' THEN 1 ELSE 0 END) AS used_total
FROM table
GROUP BY id

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 21 '06 #3
Michel Esber wrote:
>
Hello,

DB2 LUW V8 FixPack 13.

create table Table (ID varchar(20), USED char)

I need to find out the total row count per ID, as well as the row count
where USED=Y.

I could do this with a simple:

select X.ID, X.Total, Y.Total from
(select ID, COUNT(*) as Total from Table group by ID) as X
(select ID, COUNT(*) as Used_Total from Table where USED='Y' group by
ID) as Y
where X.ID=Y.ID

Depending on my table size, this query can be resource intensive and
take some time to finish.

Is there any other way to achieve the same result with an optimized
statement?

SELECT id, COUNT(*) AS total,
SUM(CASE WHEN used = 'Y' THEN 1 ELSE 0 END) AS used_total
FROM table
GROUP BY id

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Knut and Serge, thanks. My access plan is now much more efficient with
a lower cost.

-M

Nov 21 '06 #4
This will also work.
SELECT id, COUNT(*) AS total,
COUNT(CASE WHEN used = 'Y' THEN 1 END) AS used_total
FROM table
GROUP BY id

Nov 22 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Igor Kryltsov | last post: by
2 posts views Thread by Bam | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.