0

I have 3 tables:

Users

id | driverId | name

Drivers

id | userID

Jobs

id | driverID | created_at

So want total jobs count by user and by date (the job is created) also.

Result format would be:-

userID | 2019-05-11 | 2019-04-11 (created_at of job as column) ..........
1      | 2          | 5
2      | 10         | 4

I have tried with below query:-

SELECT
    COUNT(jobs.id) AS TotalJobs,
    DATE(jobs.created_at) AS created_at,
    users.id AS userID,
    users.first_name,
    users.last_name,
    jobs.driver_id AS jobsDriverID
FROM
    `jobs`
JOIN users ON users.driver_id = jobs.driver_id
GROUP BY
    DATE(jobs.created_at),
    users.driver_id
ORDER BY
    users.id ASC

And got this result but not exactly as I want.

enter image description here

kinjal jethva
  • 249
  • 2
  • 6
  • 18
  • Are you looking for a *static* column list (ie over a pre-defined list of days), or for something entirely dynamic? If you want the second option, then you would need dynamic SQL, which is much more complex. – GMB Nov 06 '19 at 13:53
  • No it should be date on which the job is created.it is fully dynamic columns of dates with jobs count. – kinjal jethva Nov 06 '19 at 13:55
  • 3
    Seriously consider handling issues of data display in application code – Strawberry Nov 06 '19 at 13:56
  • @Strawberry is right for smaller tables this is better handled by the application... – Raymond Nijland Nov 06 '19 at 14:04
  • 1
    I think I'm right regardless of the size of the table! – Strawberry Nov 06 '19 at 14:10
  • @kinjaljethva this means that you need dynamic sql. – Shadow Nov 06 '19 at 14:18
  • *"I think I'm right regardless of the size of the table!"* Well i am pretty sure you dont want to transfer very big resultsets (like 1 Gb+) on "every" request to the application to pivot @Strawberry – Raymond Nijland Nov 06 '19 at 14:50
  • @RaymondNijland I'm pretty sure you don't want to render a thousand columns to the mysql CLI. – Strawberry Nov 06 '19 at 14:59
  • *"I'm pretty sure you don't want to render a thousand columns to the mysql CLI. "* @Strawberry , i am pretty sure i would use a "materialized" view approach to generate that pivot for the application code depening if i can get away with showing a bit older data.. Anyhow we are moving way to much offtopic with unrelated comments as we don't know if a programming language or MySQL CLI is involved.. .. – Raymond Nijland Nov 06 '19 at 15:07
  • I resolved it thanks. – kinjal jethva Nov 07 '19 at 06:07

0 Answers0