Re: returning columns not in a group by

"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:41a41dff_at_news.victoria.tc.ca...
> Joe (not_at_here.com) wrote:
> : Hi
>
> : is this possible in SQLPLUS without using PL/SQL?
>
> : select count(*) col1,col2,col3
> : from table1
> : group by col1
>
> : where the count(*) returns the number in each group and I also get all
the
> : rows displayed in col1 order
>
> : Of course col2 and col3 are not in the group by so I get that error.
>
> : Is it possible?
>
> Is it possible, indeed? That depends on what you want to do, which by
> definition is not clear here since your description ... isn't.

Sorry about that. I thought it was.

> Do you mean you want all the rows displayed combined with a count for how
> many rows have the same value of col 1?

Thats it! - See it was understandable ;)

> totally untested, (i.e. I don't remember ever doing this)
>
> select
> ( select count(*) from table1 I
> where I.col1=O.col1
> ) the_count ,
> col1 ,
> col2 ,
> col3
> from table1 O
>

Thanks. It worked. I didn't realise you could return a value like that.

> Also, a useful trick for group by's is to make a string using
> concatenation of several real fields and do the group by on that string.

Righto.

Thanks again. Received on Wed Nov 24 2004 - 05:01:40 CST