Group by and order by

[jainbrijesh]

Hi friends, i am executing this query in sql server 2005 select * from users where (primaryemail like 'brijesh%@efextra.com') order by firstname and it's working well.but when i tried this one select * from users where (primaryemail like 'brijesh%@efextra.com') group by firstname it's shows me error, why? i am not getting the reason

[susarlasireesha]

the GROUP BY clause does not contain all the expressions in the SELECT clause.

[bizzzzzare]

Hi Brijesh,

To use a Group BY clause, you would need to have atleast one aggregate function in the Select Clause.

Aggregate function like Count(*), Sum, Avg etc.

All other columns excepting the one with the Aggregate function, would need to be part of the GroupBy clause.

Example.

Select METHOD_NAME, Count(*), ROUND(AVG(LAST_CALL_DURATION)/1000,3), SERVICE_NAME, RESPONSE_STATUS
From SGC.GATE_QUEUE
where SYSTEM_NAME in ('CCTOOL', 'FALLOUT_HANDLING_TOOL') -- application of interest
and date_modified >= to_date('10/03/2007 06:24 AM', 'MM/DD/YYYY HH:MI AM')
and date_modified <= to_date('10/03/2007 07:45 AM', 'MM/DD/YYYY HH:MI AM')
and METHOD_NAME = 'getPort25'
Group By SERVICE_NAME, METHOD_NAME, RESPONSE_STATUS

Cheers...

[cme_prak]

Hi,

In Group by clause, its dont accept the text datatype. Pls chk whether ur one of the column may be a text.

Thx
Prakash

[jainbrijesh]

Hi Brijesh,

To use a Group BY clause, you would need to have atleast one aggregate function in the Select Clause.

Aggregate function like Count(*), Sum, Avg etc.


Cheers...

Thanks bizzzzzare,

I got my answer.It's not required to use a Group BY clause, you would need to have atleast one aggregate function in the Select Clause.

But the case is that, the column name which you are using in select should be either in group by statement or in aggregate function.