Conditional field count

Question

If I had a table like this:

jobId, jobName, Priority 

Priority can be an integer from 1 to 5.

Since I will need this query to create a chart in the report, I will need to display the name jobid, jobname and 5 fields Priority1, Priority2, Priority3, Priority4. Priority5.

Priority1 should count the number of rows in which the priority field has a value of 1.

Priority 2 should count the number of rows in which the priority field has a value of 2.

Priority3 should count the number of rows in which the priority field has a value of 3.

etc.

How would I do it quickly and efficiently?

Thank you very much kava

Answer 1

I think you can 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'm not sure if you need jobID and JobName identifiers in the results if they delete them and delete the group,

Answer 2

Using COUNT instead of SUM cancels the requirement for the 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 

Answer 3

Using the ANSI SQL-92 CASE Statement, you can do something like this (view, 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

IIF not a standard SQL construct, but if it is supported by your database, you can get a more elegant statement that produces 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 

Answer 5

 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 6

You can join the table against yourself:

 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 can use the case inside the 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 

Answer 7

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

Something is wrong with your request design. You also show a specific task in each row, so you will either have a situation where each row has four columns with priority, column "0" and one priority with "1" (priority for this task) or you end up repeating counting for all priorities in each row.

What do you really want to show here?

Answer 8

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