Re: Group and Count Performance Issue challenge

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Tue, 13 Feb 2007 17:58:11 +0100
Message-ID: <45D1EE23.5060502@arcor.de>

Charles Hooper schrieb:
Once again, use DECODE to convert 0 to NULL and all
> other values to the original formula:
> SELECT
> BATCH,
> COUNT(BATCH) TOTAL,
> DECODE(COUNT(DECODE(STATUS,1,1,NULL)),0,NULL,COUNT(DECODE(STATUS,
> 1,1,NULL))) TOTAL_1,
> DECODE(COUNT(DECODE(STATUS,2,1,NULL)),0,NULL,COUNT(DECODE(STATUS,
> 2,1,NULL))) TOTAL_2,
> DECODE(COUNT(DECODE(STATUS,3,1,NULL)),0,COUNT(DECODE(STATUS,
> 3,1,NULL))) TOTAL_3
> FROM
> MYVIEW V1
> WHERE
> STATUS < 4
> GROUP BY
> BATCH;
>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>

Charles, count(1) and count(batch) may yield different results. I would suggest a minor modification

SELECT
BATCH,
COUNT(BATCH) TOTAL,

nullif(COUNT(DECODE(STATUS,1,batch,NULL)),0) TOTAL_1,
nullif(COUNT(DECODE(STATUS,2,batch,NULL)),0) TOTAL_2,
nullif(COUNT(DECODE(STATUS,3,batch,NULL)),0) TOTAL_3

FROM
MYVIEW V1
WHERE
STATUS < 4
GROUP BY
BATCH; Best regards

Maxim Received on Tue Feb 13 2007 - 10:58:11 CST