Home -> Usenet -> comp.databases.oracle -> SELECT question

SELECT question

From: Amine Y. Tarhini <atarhini_at_ringer.cs.utsa.edu>
Date: 1996/07/15
Message-ID: <4sf5rn$5si_at_ring01.cs.utsa.edu>#1/1 







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.
Received on Mon Jul 15 1996 - 00:00:00 CEST












Original text of this message