Conditional Count on a field

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try this   SELECT Count Student ID  as  StudentCount   FROM CourseSemOne where Student ID 3  Having Count Student ID   lt  6 and Count Student ID   gt  0

[sql] Conditional Count on a field

Examples related to sql

Examples related to count

Examples related to conditional-statements