How to SELECT Multiple COUNTs of Various Field Data?
in [Using MS SQL Server]
|
Prev: !!!!! SOFTWARE FOR SALE !!!!! � 66019
Next: EASIEST MCSE MCITP A+ CISCO MICROSOFT AND COMPTIA CERTIFICATION WITHOUT DUMPS
From: HumanJHawkins on 25 May 2010 15:02 Hi all, I'm tracking a bunch of different items through a production process. I want to be able to report out various conglomerated data about groups of items, but also include a count of items within those groupings that are at 3 different statuses (equivalent to "Not Started", "In production", and "Done") So from a data table like: Item Group Cost Status 1 A 2.50 Done 2 D 1.25 InProgress etc., etc. The desired result would be like: Group AverageCost CountPlanned CountInProgress CountDone A $3.25 7 15 23 B $45.20 15 7 2 C $2.20 200 57 125 Can you select an average of one field, and the count of multiple other fields WHERE something is true, yet GROUP BY another field? If not, how does one accomplish this? Thanks!
From: Plamen Ratchev on 25 May 2010 17:16 This is called pivoting and here is one solution: SELECT [Group], AVG(Cost) AS average_cost, COUNT(CASE WHEN Status = 'Planned' THEN 1 END) AS count_planned, COUNT(CASE WHEN Status = 'InProgress' THEN 1 END) AS count_inprogress, COUNT(CASE WHEN Status = 'Done' THEN 1 END) AS count_done FROM Items GROUP BY [Group]; -- Plamen Ratchev http://www.SQLStudio.com
From: HumanJHawkins on 25 May 2010 17:21
On May 25, 2:16 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote: > This is called pivoting and here is one solution: > > SELECT [Group], > AVG(Cost) AS average_cost, > COUNT(CASE WHEN Status = 'Planned' THEN 1 END) AS > count_planned, > COUNT(CASE WHEN Status = 'InProgress' THEN 1 END) AS > count_inprogress, > COUNT(CASE WHEN Status = 'Done' THEN 1 END) AS count_done > FROM Items > GROUP BY [Group]; > > -- > Plamen Ratchevhttp://www.SQLStudio.com Looks like just what I need. Thanks much! |