Simple SUM function needed - 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

General SQL Server Forums

New to SQL Server Programming

Simple SUM function needed

Author

Topic

parallon
Starting Member

25 Posts

Posted - 2006-07-19 : 18:07:44

Hello all. I have an Access table with EmpName, JobTask, and Hours. Multiple lines can contain the same Employee with the same JobTask. What I need to do is to list the Employee, JobTasks (grouped), and summed hours for each JobTask.

DB Ex:

John Doe      Welding    8    
John Doe      Cleaning   4    
Bubba Smith   Fork Lift  3
John Doe      Welding    7    
Steve Johnson Welding    5
Bubba Smith   Fork Lift  6

Page output:

John Doe
  Welding       15 hrs.
  Cleaning       4 hrs.

Bubba Smith
  Fork Lift      9 hrs.

Steve Johnson
  Welding        5 hrs.

This is how I figured it should be, but it's not working for me:

SELECT EmpName, SUM (Hours), JobTask
FROM tblEmpTime
Group By JobTask

Any suggestions would be greatly appreciated.

Thanks,

Parallon

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-19 : 18:12:08

Almost right:

SELECT EmpName, SUM (Hours), JobTask
FROM tblEmpTime
Group By EmpName,JobTask

You'll need to do the formatting (e.g. indenting) in your Access form/report for display purposes.
It is possible to do this in a query, but it wouldn't be recommended unless there was no other option. It involves UNION queries and can get a bit messy for a noob.

HTH,

Tim

parallon
Starting Member

25 Posts

Posted - 2006-07-19 : 18:48:01

Thank you very much. That worked perfectly.

Parallon

prajey
Starting Member

2 Posts

Posted - 2006-07-20 : 05:42:49

select Empname, JobTask, sum(Hours) from tblEmpTime Group By Empname,JobTask Order By EmpName

This would be more perfect

Prabhakar Jeyaraman

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-20 : 06:02:42

Why is that more "perfect" than

select Empname, JobTask, sum(Hours) from tblEmpTime Group By Empname,JobTask Order By sum(Hors) DESC, empname

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources