Posted by Alex Kuznetsov on 06/21/07 17:21

On Jun 21, 12:06 pm, "webdevacco...@gmail.com"
<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/

[Back to original message]