Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Group and Count Performance Issue challenge

Re: Group and Count Performance Issue challenge

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 13 Feb 2007 09:31:29 -0800
Message-ID: <1171387889.008790.185850@m58g2000cwm.googlegroups.com>


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

FROM
  MYVIEW V1
WHERE
  STATUS < 4
GROUP BY
  BATCH; Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Feb 13 2007 - 11:31:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US