Conditional Count on a field

Question

Hello,

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?

Many Thanks, Kave

Answer 1

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

Answer 2

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?

Answer 3

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


)

Answer 4

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 5

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