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!

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

On May 25, 2:16*pm, Plamen Ratchev <Pla... (AT) SQLStudio (DOT) com> wrote:
Looks like just what I need. Thanks much!