Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Group and Count Performance Issue challenge
On Feb 13, 11:58 am, Maxim Demenko <mdeme..._at_arcor.de> wrote:
> 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- Hide quoted text -
Maxim, good catch. COUNT(BATCH) and COUNT(1) could be different values if null values are permitted in the BATCH column.
If nulls are not permitted in the BATCH column, this should also work:
SELECT
BATCH,
SUM(1) TOTAL, SUM(DECODE(STATUS,1,1,NULL)) TOTAL_1, SUM(DECODE(STATUS,2,1,NULL)) TOTAL_2, SUM(DECODE(STATUS,3,1,NULL)) TOTAL_3