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?
This question is related to
sql
count
conditional-statements
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
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
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;
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
Source: Stackoverflow.com