CODEWITHSUNDEEP
sqlsql-server
James Naughton
James Naughton

Reputation: 1

SQL Counting of Different Values

I am trying to figure out how to put the count of a field when it equals different conditions.

For instance I have a 

SELECT DISTINCT FileName, ReasonCode
from TBL1

where I want to be able to select a distinct file name and show the count of ReasonCode = x (name this column count of x), ReasonCode = y (name this column count of y), ReasonCode = z (count of z).

I want to output all of this one 1 row for that distinct file name.

So that it has a FileName field, count of x field, count of y field, and count of z field

Any suggestions?

Upvotes: 0

Views: 56

Answers (1)

juergen d
juergen d

Reputation: 204756

SELECT FileName,
       sum(case when ReasonCode = 'x' then 1 else 0 end) as x_count,
       sum(case when ReasonCode = 'y' then 1 else 0 end) as y_count,
       sum(case when ReasonCode = 'z' then 1 else 0 end) as z_count
from TBL1
group by FileName

Upvotes: 2

Related Questions