Re: Join question

Hi Joe

I think that you should be able to do this:

SELECT table1.col1, table2,col2, count(table1.*) FROM table1, table2
WHERE table1.col1 = table2.col1
GROUP BY table1.col1, table2,col2
/

You DON'T NEED to do a DISTINCT, as you are GROUPing by the col1 column!! The only other thing you need to do is to add the second column, col2, to your SELECT and GROUP BY lines.

Hope this is of help.

Mark Griffiths
Freelance Oracle Consultant

On Fri, 6 Mar 1998 09:54:46 -0500, "J Kaczor" <kaczor_at_care.org> 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
>
>
>
Received on Sat Mar 14 1998 - 00:00:00 CST