Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Join question
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
HTH
-- Chrysalis FABRICATI DIEM, PVNC ('To Protect and to Serve') Terry Pratchett : "Guards, Guards"Received on Fri Mar 06 1998 - 00:00:00 CST