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: Join question

Re: Join question

From: Chrysalis <cellis_at_iol.ie>
Date: 1998/03/06
Message-ID: <3500744A.4CBE@iol.ie>#1/1

J Kaczor wrote:
>
> Hello all,
>
> We have a problem joining two tables and are
> wondering if anyone can help us. We want to get
> a count and distinct value (select disinct col1, count(col1))
> from one table, and a value for a code stored in table2
> that is correlated to the distinct value in table1.col1..
>
> i.e.
> table1
> (col1 varchar(10))
> table2
> (col1 varchar2(10) -- values from table1.col1
> col2 varchar2(5) -- our code
>
> We are having trouble grouping the results of the select together..
> we tried the following SQL
>
> select distinct 1.col1, count(1.col1), 2.col2
> from table1 1, table2 2
> where 1.col1 = 2.col1
> group by col1;
>
> and we get an invalid group by (for col2, which
> makes sense.. we aren't doing a count/group function
> on it)..
> How can we get a result set like...
>
> 1.col1 count(1.col1) value(2.col2)
> --------- ----------------- -------------------
> value1 15,000 1-50
> value2 3,500 51-100
> valuex 20,000 300-500
> valuey 100 700-710
>
> Thanks in advance,
> Joe

Your query is attempting to aggregate a join (of table1 and table2), when what you should be doing is to join a table (table2) to an aggregate (of table1).
Try the following:

select T1.ct,T2.col2
from (select col1,count(*) ct

       from table1
       group by col1) T1
     ,table2 T2

where T2.col1 = T1.col1

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards, Guards"
Received on Fri Mar 06 1998 - 00:00:00 CST

Original text of this message

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