I've got a stored procedure which has retrieved a year's worth of order history and has added an analysis code to each line.
Simplistically the table looks like ...
ID, ProductCode, Analysis
I want a result set of
ProductCode, Count(*), count where analysis = 'A', count where analysis = 'B', count where analysis = 'C', count of unanalyzed.
I've got ...
COUNT(NULLIF(Analysis, 'NONIOC/Acc/Unit')) AS [NONIOC/Acc/Unit]
but I want the number of NONIOC/Acc/Unit...
COUNT(*) - COUNT(NULLIF(Analysis, 'NONIOC/Acc/Unit')) AS [NONIOC/Acc/Unit]
gives me that. But is there a more succinct way?
I also need to do SUM()s for AnalysisValue for each of my Analysis.
Using Microsoft SQL Server 2005