Re: Group by: Not getting expected output

From: Carlos <miotromailcarlos_at_netscape.net>
Date: 11 May 2006 23:45:23 -0700
Message-ID: <1147416323.783011.98580@j33g2000cwa.googlegroups.com>

>>I doubt very much it does.

You, faithless man! ;-)

Of course it does:

Conectado a:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production

sql> create table info (tcode char(1) not null, tavg number not null);

Tabla creada.

sql> insert into info values ('1',1);

1 fila creada.

sql> insert into info values ('2',2);

1 fila creada.

sql> insert into info values ('3',3);

1 fila creada.

sql> insert into info values ('4',4);

1 fila creada.

sql> insert into info values ('5',5);

1 fila creada.

sql> insert into info values ('6',1);

1 fila creada.

sql> insert into info values ('7',2);

1 fila creada.

sql> insert into info values ('8',2);

1 fila creada.

sql> insert into info values ('9',3);

1 fila creada.

sql> insert into info values ('2',2);

1 fila creada.

sql>
sql> SELECT a.tcode, NVL(b.GradeEq2to3,0)   2 FROM ( SELECT distinct tcode

  3             FROM info) a,
  4         ( SELECT tcode,
  5                  COUNT(*) AS GradeEq2to3
  6             FROM info
  7            WHERE tavg >= 2
  8              AND tavg < 3
  9         GROUP BY tcode ) b

 10 WHERE a.tcode = b.tcode(+);

T NVL(B.GRADEEQ2TO3,0)

- --------------------
1                    0
2                    2
3                    0
4                    0
5                    0
6                    0
7                    1
8                    1
9                    0

9 filas seleccionadas.

sql>

A long time ago in a galaxy far, far away (Oracle 7)... there were no CASE sentences...

Cheers.

Carlos. Received on Fri May 12 2006 - 01:45:23 CDT