Conditional Count on a field

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?

8 Answers

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,

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 

IIF is not a standard SQL construct, but if it's supported by your database, you can achieve a more elegant statement producing the same result:

SELECT JobId, JobName, COUNT(IIF (Priority=1, 1, NULL)) AS Priority1, COUNT(IIF (Priority=2, 1, NULL)) AS Priority2, COUNT(IIF (Priority=3, 1, NULL)) AS Priority3, COUNT(IIF (Priority=4, 1, NULL)) AS Priority4, COUNT(IIF (Priority=5, 1, NULL)) AS Priority5 FROM TableName GROUP BY JobId, JobName 

Using ANSI SQL-92 CASE Statements, you could do something like this (derived table plus case):

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

SELECT Priority, COALESCE(cnt, 0) FROM ( SELECT 1 AS Priority UNION ALL SELECT 2 AS Priority UNION ALL SELECT 3 AS Priority UNION ALL SELECT 4 AS Priority UNION ALL SELECT 5 AS Priority ) p LEFT JOIN ( SELECT Priority, COUNT(*) AS cnt FROM jobs GROUP BY Priority ) j ON j.Priority = p.Priority 

You could join the table against itself:

select t.jobId, t.jobName, count(p1.jobId) as Priority1, count(p2.jobId) as Priority2, count(p3.jobId) as Priority3, count(p4.jobId) as Priority4, count(p5.jobId) as Priority5 from theTable t left join theTable p1 on p1.jobId = t.jobId and p1.jobName = t.jobName and p1.Priority = 1 left join theTable p2 on p2.jobId = t.jobId and p2.jobName = t.jobName and p2.Priority = 2 left join theTable p3 on p3.jobId = t.jobId and p3.jobName = t.jobName and p3.Priority = 3 left join theTable p4 on p4.jobId = t.jobId and p4.jobName = t.jobName and p4.Priority = 4 left join theTable p5 on p5.jobId = t.jobId and p5.jobName = t.jobName and p5.Priority = 5 group by t.jobId, t.jobName 

Or you could use case inside a sum:

select jobId, jobName, sum(case Priority when 1 then 1 else 0 end) as Priority1, sum(case Priority when 2 then 1 else 0 end) as Priority2, sum(case Priority when 3 then 1 else 0 end) as Priority3, sum(case Priority when 4 then 1 else 0 end) as Priority4, sum(case Priority when 5 then 1 else 0 end) as Priority5 from theTable group by jobId, jobName 

I would need to display the jobid, jobname and 5 fields called Priority1, Priority2, Priority3, Priority4. Priority5.

Something's wrong with your query design. You're showing a specific job in each row as well, and so you'll either have a situation where ever row has four priority columns with a '0' and one priority column with a '1' (the priority for that job) or you'll end up repeating the count for all priorities on every row.

What do you really want to show here?

Try this:

SELECT Count(Student_ID) as 'StudentCount' FROM CourseSemOne where Student_ID=3 Having Count(Student_ID) < 6 and Count(Student_ID) > 0;