Use order by and group by together

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Use order by and group by together

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2009-01-06 : 01:44:50

hi
how to write select query use sum function and group by and order by clouse together

eg:
select name , sum(sal) from emp where name='abc' group by name order by date

thanks

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-01-06 : 01:49:59

You can't because it doesn't make sense.
Your grouping & aggregates are across many rows so each aggreagated row has no such concept of a single date.
Try writing some input & output and you'll see why.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 01:54:52

quote:
Originally posted by amirs

hi
how to write select query use sum function and group by and order by clouse together

eg:
select name , sum(sal) from emp where name='abc' group by name order by date

thanks

i think u will get a error date is not present in group by clause
so u cann't use date in order by clasue
select name , sum(sal) from emp where name='abc' group by name order by name

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-06 : 01:59:16

date is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
so, u got an error while executing query, please check it once

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2009-01-06 : 01:59:32

thank both to replay

but i have select name and sum(sal) where emp='abc' and date descding order so how i can posible

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-06 : 02:03:54

Add date Field in the select List or Add date field in the group by clause

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 02:04:44

try like this

select e.empid,e.sal
from urtable et
cross apply
(select empid,sum(empsal)as sal from urtable group by empid) e
order by et.date

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-01-06 : 02:05:14

It. Is. Not. Possible.
Write it out the data and try to do it. You cannot. You need to understand why, not just keep asking over & over.
Aggregated rows do not have a date to sort on because there is no single date that applies to it.
Any order you see is purely coincidental.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 02:36:43

i cant even make out whats was need of this reqmnt. perhaps you could explain us with some sample data so that we can understand what your exact scenario is

achadili
Starting Member

2 Posts

Posted - 2009-04-22 : 11:43:54

quote:
Originally posted by LoztInSpace

It. Is. Not. Possible.
Write it out the data and try to do it. You cannot. You need to understand why, not just keep asking over & over.
Aggregated rows do not have a date to sort on because there is no single date that applies to it.
Any order you see is purely coincidental.

Can you provide an alternative? You just say: We can't but you don't propose the answer to the issue. It is legitimate to wanna group and sort the groupings by a field in the results like counts, sums etc... So how should we do that in SQL Server 2000?

Thanks

achadili
Starting Member

2 Posts

Posted - 2009-04-22 : 11:49:49

quote:
Originally posted by visakh16

i cant even make out whats was need of this reqmnt. perhaps you could explain us with some sample data so that we can understand what your exact scenario is

Here is an example

PartnerID, protocol, procdate, status

AAAAAAAAA FILE 1/1/2009, OK
AAAAAAAAA FTP 1/2/2009, OK
BBBBBBBBB HTTP 1/6/2009, OK
BBBBBBBBB FTP 2/2/2009, OK
BBBBBBBBB FILE 3/1/2009, OK
CCCCCCCCC FTP 4/1/2009, OK

I want to have (group by Partner and sort by count)

PartnerID Count
BBBBBBBBB 3
AAAAAAAAA 2
CCCCCCCCC 1

How to do that in SQL Server 2000?

Thanks

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-22 : 14:32:38

select count(*) as cnt,partnerid from urtable group by partnerid order by cnt desc

may be like this...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources