Conditional Count on a field

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?

Many Thanks, Kave

Answer by Quassnoi for Conditional Count on a field

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 by Joel Coehoorn for Conditional Count on a field

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 by Anon246 for Conditional Count on a field

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 by David Raznick for Conditional Count on a field

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 by Guffa for Conditional Count on a field

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  

Answer by taur for Conditional Count on a field

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  

Answer by Guy Levin for Conditional Count on a field

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  

Answer by Em Aamir for Conditional Count on a field

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

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72