SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Group By and Count(*)		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Spica66
Starting Member

23 Posts
Posted - 10/01/2012 :  15:56:16  Show Profile  Reply with Quote
I have a table with quantities of inventory tags assigned to different plants.
Some of the plants have more than one group assigned to them. For display, I want the total assigned to that plant.

This code:

SELECT t.PlantID
,Sum(t.QtyAssigned)qty

FROM dbo.inv_TagAssignments t


GROUP BY t.plantID, t.qtyAssigned

Order by PlantId asc

Gives this result:
P10 4500
P11 400
P12 600
P14 700
P17 200
P18 100
P18 300
P2 6500
P3 400
P5 500
P5 5500
P69A 3000
P69B 6000
P7A 3500
P7B 5000
P9 300

As you can see, P5 is listed twice. How can I rewrite this query to group P5 together and show the total qty assigned as 6000?
Edited by - Spica66 on 10/01/2012 15:59:23

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 10/01/2012 :  16:10:02  Show Profile  Reply with Quote 

SELECT t.PlantID
,Sum(t.QtyAssigned)qty
FROM dbo.inv_TagAssignments t
GROUP BY t.plantID
Order by PlantId asc


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1430 Posts
Posted - 10/01/2012 :  16:12:04  Show Profile  Reply with Quote
Don't GROUP BY qtyAssigned.
SELECT t.PlantID
,Sum(t.QtyAssigned)qty
FROM dbo.inv_TagAssignments t
GROUP BY t.plantID --, t.qtyAssigned
Order by PlantId asc



=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000