• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 910
  • Last Modified:

How do I get opposite value to COUNT(NULLIF(x,y))


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
Richard Quadling
Richard Quadling
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the CASE statement might be what you are looking for
, SUM( CASE WHEN Analysis = 'NONIOC/Acc/Unit' THEN 1 ELSE 0 END) AS [NONIOC/Acc/Unit]
, SUM( CASE WHEN Analysis = 'NONIOC/Acc/Unit' THEN 0 ELSE 1 END) AS [NOT NONIOC/Acc/Unit]

Open in new window

Richard QuadlingSenior Software DeverloperAuthor Commented:
Aha! Yes.

I thought I tried that. Seems I get my syntax all messedup.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now