Posted by Ed Murphy on 06/26/07 15:54

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)

[Back to original message]