SUM and group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SUM and group by

Author

Topic

cjcclee
Starting Member

33 Posts

Posted - 2007-11-19 : 11:16:59

I have a table like below, I import this table form payroll database.
import id and sequence id are primary key for the table
some employee may have more than one paycheck for each payroll date,
So I need group by import id, ss, date, then get the SUM(wage) for each employee each week.
select SUM(Wage), ss,date, import id
from table a
group by ss, date, import id

SS |wage| date|Import ID| sequence ID|

but I also want get the sequence id for each employee(if employee has more than two paycheck, I want the first sequence id.

for example,

1111 1000 11/17/2007 1 0
1111 900 11/17/2007 1 1
2222 800 11/17/2007 1 2
3333 700 11/17/2007 1 3
1111 1000 11/24/2007 2 0
2222 900 11/24/2007 2 1
. . . 2 2

I want get result as following:

1111 1900 11/17/2007 1 0
2222 800 11/17/2007 1 2
3333 700 11/17/2007 1 3
1111 1000 11/24/2007 2 0
2222 900 11/24/2007 2 1

how should I write the query to get the result! Thanks!

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-11-19 : 11:22:30

select min([sequence id]) as min_sid, SUM(Wage), ss,date, import id
from table a
group by ss, date, import id

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

cjcclee
Starting Member

33 Posts

Posted - 2007-11-19 : 11:39:49

Thanks so much Jhocutt!
I tried so many corelated subquery, never think of the solution can be so simple. You are so smart! Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources