Conditional Count on a field

Question

If I had a table like this:

jobId, jobName, Priority 

Whereby Priority can be an integer between 1 to 5.

Since I would need this query for generating a chart on report, I would need to display the jobid, jobname and 5 fields called Priority1, Priority2, Priority3, Priority4. Priority5.

Priority1 should count the amount of rows where priority field has the value of 1.

Priority2 should count the amount of rows where priority field has the value of 2.

Priority3 should count the amount of rows where priority field has the value of 3.

etc

How would I do that in a quick and performant manner?

Answer 1

I think you may be after

select      jobID, JobName,     sum(case when Priority = 1 then 1 else 0 end) as priority1,     sum(case when Priority = 2 then 1 else 0 end) as priority2,     sum(case when Priority = 3 then 1 else 0 end) as priority3,     sum(case when Priority = 4 then 1 else 0 end) as priority4,     sum(case when Priority = 5 then 1 else 0 end) as priority5 from     Jobs group by      jobID, JobName 

However I am uncertain if you need to the jobID and JobName in your results if so remove them and remove the group by,

Answer 2

Using COUNT instead of SUM removes the requirement for an ELSE statement:

SELECT jobId, jobName,     COUNT(CASE WHEN Priority=1 THEN 1 END) AS Priority1,     COUNT(CASE WHEN Priority=2 THEN 1 END) AS Priority2,     COUNT(CASE WHEN Priority=3 THEN 1 END) AS Priority3,     COUNT(CASE WHEN Priority=4 THEN 1 END) AS Priority4,     COUNT(CASE WHEN Priority=5 THEN 1 END) AS Priority5 FROM TableName GROUP BY jobId, jobName