Conditional Count on a field
SqlCountConditional StatementsSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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,
Solution 2 - Sql
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
Solution 3 - Sql
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
Solution 4 - Sql
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
)
Solution 5 - Sql
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
Solution 6 - Sql
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
Solution 7 - Sql
> 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?
Solution 8 - Sql
Try this:
SELECT Count(Student_ID) as 'StudentCount'
FROM CourseSemOne
where Student_ID=3
Having Count(Student_ID) < 6 and Count(Student_ID) > 0;