473,322 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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!

Jun 21 '07 #1
2 2677
On Jun 21, 12:06 pm, "webdevacco...@gmail.com"
<webdevacco...@gmail.comwrote:
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/

Jun 21 '07 #2
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, "we***********@gmail.com"
<we***********@gmail.comwrote:
>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!
Jun 21 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: Lauren Quantrell | last post by:
I have a stored procedure using Convert where the exact same Convert string works in the SELECT portion of the procedure but fails in the WHERE portion. The entire SP is listed below....
15
by: | last post by:
The data file is a simple Unicode file with lines of text. BCP apparently doesn't guarantee this ordering, and neither does the import tool. I want to be able to load the data either sequentially...
10
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I...
16
by: Tim Davidge | last post by:
Hi folks, been a while since I have posted a plea for help and I think I have forgotten everything I learnt from the helpful contributors to this newsgroup, that said however : I'm trying to...
6
by: Rudolf Bargholz | last post by:
Hi , I have the following tables ------------- PAX: Id Order_Id Name Position
1
by: Prasad Karunakaran | last post by:
I am using the C# DirectoryEntry class to retrieve the Properties of an user object in the Active Directory. I need to get the First Name and Last Name as properties. I know it is not supported...
1
by: melanieab | last post by:
Hi again, I'm trying to programatically sort a datagrid. I did find the following code, and it does work, but, when a column header is clicked, the data only sorts in descending order. Clicking...
3
by: fstenoughsnoopy | last post by:
Ok the complete story. I have a Contact Table, Query and Form, that are used to input and store the contact info for customers. They have FirstName, LastName and Address as the primary key...
7
by: Serge Rielau | last post by:
Hi all, Following Ian's passionate postings on problems with ALTOBJ and the alter table wizard in the control center I'll try to explain how to use ALTOBJ with this thread. I'm not going to get...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.