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!

On Jun 21, 12:06 pm, "webdevacco... (AT) gmail (DOT) com"
<webdevacco... (AT) gmail (DOT) com> wrote:
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/

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 (AT) gmail (DOT) com"
<webdevaccount (AT) gmail (DOT) com> wrote:

webdevaccount (AT) gmail (DOT) com wrote:

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)