Re: returning columns not in a group by

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.

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

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

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. Received on Tue Nov 23 2004 - 23:37:03 CST