Group By and Count(*) - 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 2008 Forums

Transact-SQL (2008)

Group By and Count(*)

Author

Topic

Spica66
Starting Member

23 Posts

Posted - 2012-10-01 : 15:56:16

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 16:10:02

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

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-01 : 16:12:04

Don't GROUP BY qtyAssigned.[CODE]SELECT t.PlantID
,Sum(t.QtyAssigned)qty
FROM dbo.inv_TagAssignments t
GROUP BY t.plantID --, t.qtyAssigned
Order by PlantId asc[/CODE]

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources