Re: SELECT question(B

From: Ken Mizuta(B <kmizuta_at_jp.oracle.com>
Date: 1996/07/16
Message-ID: <31EBD80F.4C1_at_jp.oracle.com>#1/1

Amine Y. Tarhini wrote:
>
> I appreciate any help I get to this question. The environment is ORACLE
> REPORTS 2.5, but the question is strictly SQL.
>
> Suppose:
>
> table1 table2 table3
> ------ ------- -------
> col1 col1 col2 col3 col1 col2
> ----- ---- ----- ---- ---- ----
> A1 A1 B1 C1 A1 B1
> A1 B1
> A1 B2
> A1 B2
> if I do
> select sum(decode(table2.col3,'C1',1,0))
> from table2,table1
> where table2.col2 = 'B1'
> and table2.col1 = table1.col1
> and table1.col1 = 'A1'
> I get 1, correct
>
> and If I do
> select count(distinct table3.col2)
> from table3,table1
> where table3.col1 = table1.col1
> and table1.col1 = 'A1'
> I get 2, correct
>
> I have to have one query for the above, but If I do
> select table1.col1 first,
> sum(decode(table2.col3,'C1',1,0)) second,
> count(distinct table3.col2) third
> from table1, table2, table3
> where table2.col1 = table1.col1
> and table2.col2 = 'B1'
> and table3.col1 = table1.col1
> and table1.col1 = 'A1'
> group by table1.col1
>
> I get:
> first second third
> ----- ------ -----
> A1 2 2
>
> In the actual query, I get for "second" column whatever the answer should be,
> multiplied by the whatever I get for "third" column.
>
> I hope I modeled the example correctly after the actual query. Thanks again
> in advance for any help.

I'm not quite sure what your question is, but this is what is happening. If you were to do a

select table1.col1 first,
       table2.col3 second,
       table3.col2 third

 from table1, table2, table3
 where <same where clause>
 group by table1.col1;

You would get

first	second	third
------	------	------
A1	C1	B1
A1	C1	B1
A1	C1	B2
A1	C1	B2

Since you are counting distinct instances of table3.col2, if you get rid of the duplicates you get:

For this, there are 4 C1's so the second column becomes 4, and there are 2 distinct values for the third column so that becomes 2.

The answer I got when I tried your SQL script was:

first	second	third
------	------	------
A1	4	2



-- 
                  ____________________________________________ 
                 /    Kenichi Mizuta 
      ////      /     Oracle Corporation (Redwood Shores, CA) 
     |0 0|     /      Applications Division 
_ooO_ \U/_Ooo_/       email: kmizuta_at_us.oracle.com 

The comments and opinions expressed herein are mine and do not necessarily represent those of Oracle Corporation. Received on Tue Jul 16 1996 - 00:00:00 CEST