Using the same column in bother SUM and ORDER BY? How?

Hello,

Using SQL 2005. Columns:

ID, int (PK, auto-increment by 1)
WorkHours, int
Name, varchar(100)

I can't seem to get the following query to work. I want to return all
Names and the sum of ALL work hours, in each row and order by each
INDIVIDUAL work hour:

SELECT Name, SUM(WorkHours) as h
FROM Employers
ORDER BY WorkHours DESC

It seems that putting WorkHours in but the aggregate function and the
ORDER BY clause creates a problem.

Thank you for your help!

Re: Using the same column in bother SUM and ORDER BY? How?

On Jun 21, 12:06 pm, "webdevacco...@gmail.com"
wrote:
> Hello,
>
> Using SQL 2005. Columns:
>
> ID, int (PK, auto-increment by 1)
> WorkHours, int
> Name, varchar(100)
>
> I can't seem to get the following query to work. I want to return all
> Names and the sum of ALL work hours, in each row and order by each
> INDIVIDUAL work hour:
>
> SELECT Name, SUM(WorkHours) as h
> FROM Employers
> ORDER BY WorkHours DESC
>
> It seems that putting WorkHours in but the aggregate function and the
> ORDER BY clause creates a problem.
>
> Thank you for your help!

CREATE TABLE #t(ID INT, wh INT)
INSERT #t VALUES(1, 2)
INSERT #t VALUES(1, 3)
INSERT #t VALUES(1, 4)
INSERT #t VALUES(1, 2)

INSERT #t VALUES(2, 12)
INSERT #t VALUES(2, 3)
INSERT #t VALUES(2, 4)
INSERT #t VALUES(2, 2)

SELECT ID, SUM(wh) OVER(PARTITION BY ID), wh FROM #t ORDER BY wh

ID wh
----------- ----------- -----------
1 11 2
1 11 2
2 21 2
2 21 3
1 11 3
1 11 4
2 21 4
2 21 12

http://sqlserver-tips.blogspot.com/

Re: Using the same column in bother SUM and ORDER BY? How?

If you post a question to multiple groups is is best to put all the
group names on the same message so that all the discussions are tied
together.

See my reply in microsoft.public.sqlserver.programming

Roy Harvey
Beacon Falls, CT

On Thu, 21 Jun 2007 10:06:32 -0700, "webdevaccount@gmail.com"
wrote:

>Hello,
>
>Using SQL 2005. Columns:
>
>ID, int (PK, auto-increment by 1)
>WorkHours, int
>Name, varchar(100)
>
>I can't seem to get the following query to work. I want to return all
>Names and the sum of ALL work hours, in each row and order by each
>INDIVIDUAL work hour:
>
>SELECT Name, SUM(WorkHours) as h
>FROM Employers
>ORDER BY WorkHours DESC
>
>It seems that putting WorkHours in but the aggregate function and the
>ORDER BY clause creates a problem.
>
>Thank you for your help!

Re: Using the same column in bother SUM and ORDER BY? How?

webdevaccount@gmail.com wrote:

> Using SQL 2005. Columns:
>
> ID, int (PK, auto-increment by 1)
> WorkHours, int
> Name, varchar(100)
>
> I can't seem to get the following query to work. I want to return all
> Names and the sum of ALL work hours, in each row and order by each
> INDIVIDUAL work hour:
>
> SELECT Name, SUM(WorkHours) as h
> FROM Employers
> ORDER BY WorkHours DESC
>
> It seems that putting WorkHours in but the aggregate function and the
> ORDER BY clause creates a problem.

Suppose your data looks like this:

ID WorkHours Name
-------------------
1 7 Jim
2 5 Bob
3 4 Bob
4 1 Jim

so your aggregate data looks like this, ignoring sort order:

Name h
-------
Jim 8
Bob 9

Do you want Jim or Bob to be sorted first? Why?

I suspect that you want either
ORDER BY SUM(WorkHours)
or
ORDER BY MAX(WorkHours)