Re: No subselect in SELECT clause?

From: MARC, MAZEROLLE <mazerolle.marc_at_bell.ca>
Date: Fri, 22 Oct 1999 09:26:41 -0400
Message-ID: <38106611.DA41DE8@bell.ca>

Don't you mean ?

The result I want is

A       3
A       3
B       4
B       4

I guess MSSQL accepts sub-selects in select part but does not do a good job at it.....

Try this :

SQL> select * from t1;

COL1 COL2
---------- ---------

A                  1
A                  2
B                  1
B                  3

SQL> select a.col1, b.col2
  2 from t1 a,

  3         (select col1,sum(col2) col2
  4            from t1
  5           group by col1) b

  6 where a.col1 = b.col1;

COL1 COL2
---------- ---------

A                  3
A                  3
B                  4
B                  4

SQL> Regards,

Marc Mazerolle
InforMaze Technologies.

Franz Mueller a écrit:
>
> Hi,
> I have the following problem:
>
> Col1 Col2
> A 1
> A 2
> B 1
> B 3
>
> The result I want is
> A 2
> A 2
> B 4
> B 4
> where the second number is the sum of the As or Bs
>
> In MSSQL Transact-SQL I can do the following:
>
> SELECT col1 w1 , (select sum(col2) from t1 where col1=w1) from t1
> which returns exactly what I want.
>
> How can I do this in ORACLE (which does not accept subselects in the
> SELECT part)?
>
> Franz
Received on Fri Oct 22 1999 - 08:26:41 CDT