Using Sum - 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

Using Sum

Author

Topic

Mortal Wombat
Starting Member

10 Posts

Posted - 2010-11-30 : 05:38:07

Hi All,

I'm working through what should hopefully be a simple query and was hoping for a little guidance.

I have a one to many relationship for an employee selecting items as such :

 SELECT
	  e.staffNumber,
	  e.firstName+' '+e.surname as [name],
          i.name as [itemName],
	  eis.selectionQuantity as [currentSelection]
	FROM				
	  Employee e 
	JOIN EmployeeItemSelection eis on e.id = eis.employeeId
	JOIN Item i on eis.itemId = i.id

This yields results in the following format :

quote:

staffNumber name itemName currentSelection
123456 Joe Bloggs toy 1 0
123456 Joe Bloggs toy 2 20
123456 Joe Bloggs toy 3 0
123456 Joe Bloggs toy 4 20
246801 Jack Black toy 1 0
246801 Jack Black toy 2 10

I'm now trying to SUM together the currentSelections into a new column summing all the values in the currentSelection column for each employee. E.g. Joe Bloggs would have a value of 40, Jack Black a value of 10.

I tried the following but because each item is unique I'm getting a sum for each unique toy as opposed to each employee:

 SELECT
	  e.staffNumber,
	  e.firstName+' '+e.surname as [name],
          i.name as [itemName],
	  eis.selectionQuantity as [currentSelection]
          SUM (eis.selectionQuantity)
	FROM				
	  Employee e 
	JOIN EmployeeItemSelection eis on e.id = eis.employeeId
	JOIN Item i on eis.itemId = i.id
	GROUP BY
           e.staffNumber,
	  e.firstName+' '+e.surname],
          i.name,
	  eis.selectionQuantity

Can anyone advise of how I can achieve this. My ultimate goal is to add this extra sum column and then pivot the results so I have one row per employee and the sum of their current selection appended to the row.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-30 : 05:44:37

In your group by take away the quantity.

No, you're never too old to Yak'n'Roll if you're too young to die.

Mortal Wombat
Starting Member

10 Posts

Posted - 2010-11-30 : 05:58:09

Thanks webfred,

I was hoping to return the item name, quantity selected of that particular item and then my new sum column showing the total quantity selected for each employee. So by removing the quantity in the group by I wouldn't be able to achieve this.

I realise that by having the item name and/or quantity selected in my select query and group by I am getting a SUM for each individual row which is not the desired result. I would like to see :

staffNumber name itemName currentSelection Total
123456 Joe Bloggs toy 1 0 40
123456 Joe Bloggs toy 2 20 40
123456 Joe Bloggs toy 3 0 40
123456 Joe Bloggs toy 4 20 40
246801 Jack Black toy 1 0 10
246801 Jack Black toy 2 10 10

Hope this makes sense...

Blue elf has reflective shot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-01 : 12:49:29

if sql 2005 or above


SELECT staffNumber,	name,	itemName, currentSelection,Total
FROM
(
SELECT staffNumber,	name,	itemName, currentSelection,SUM(currentSelection) OVER (PARTITION BY staffNumber,name) AS Total
FROM table
)t


if sql 2000 or below use

SELECT t.staffNumber,	t.name,	t.itemName, t.currentSelection,t1.Total
FROM Table t
INNER JOIN (SELECT staffNumber,	name,SUM(currentSelection) AS Total
FROM Table
GROUP BY staffNumber,	name) t1
ON t1.staffNumber = t.staffNumber
AND t1.name = t.name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources