I have set of jobs record for whole month and want to display qty of jobs date wise in SQL

Question

We have thousands of record in our data and want to count date wise jobs with category through single query. It is Possible? Display required as under

TypesJobs   01  02  03 04 05 06 07        
A            2  1    6  4  1  3  4
B            10 12   8  10 12 9  13
C            3   5   4   3  2  5  4

Here Types of jobs count for a day in date column 01, 02, 03 are date range of the month

If you're using MySQL: https://stackoverflow.com/questions/7674786/mysql-pivot-table — Tim Biegeleisen, Apr 30 '18 at 06:40

score 1 · Answer 1 · answered Apr 30 '18 at 11:19

You can use conditional aggregation, something like this:

select typesjobs,
       sum(case when month(datecol) = 1 then 1 els e0 end) as month_01,
       sum(case when month(datecol) = 2 then 1 els e0 end) as month_02,
       . . .
from t
where <date condition here>
group by typesjobs;

I have set of jobs record for whole month and want to display qty of jobs date wise in SQL

1 Answers1