Optimized statement for subquery using count(*)

[Michel Esber]

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

[Serge Rielau]

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

[Knut Stolze]

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

[Michel Esber]

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

[Tonkuma]

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