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)

• Next in forum: Re: MSSQL - DTS Package - Find distinct rows - Output to TXT file - ActiveX?
• Prev in forum: Re: Database design question - Isolated, unrelated tables
• Thread view: Re: Using the same column in bother SUM and ORDER BY? How?

[Reply to this message]