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: returning columns not in a group by

Re: returning columns not in a group by

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 23 Nov 2004 21:37:03 -0800
Message-ID: <41a41dff@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.

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

Original text of this message

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