Show SQL grouped data for each day in a date range

Question

I'm using a SQL query to count machine errors between a date range for each machine type.

Here's my code :

SELECT  
    machine_type, 
    COUNT(DISTINCT ID_machine) 
FROM
    MACHINE 
WHERE 
    ID_MACHINE IN (SELECT DISTINCT MACHINE_ID_machine 
                   FROM TRACE 
                   WHERE (date_file BETWEEN '2020-10-01' AND '2020-10-02') 
                     AND (trace_status = 'OK')) 
GROUP BY
    machine_type

It's working but shows the data on the whole date range and not for every day in the date range, can someone show me how to split the dates and count for each day in the specified date range, thank you all

Please provide sample data and desired results. It is not at all clear what results you want. For instance, you are not selecting any dates in the result set, despite the title of the question. — Gordon Linoff, Oct 05 '20 at 12:35
See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Oct 05 '20 at 12:54
If you want the results by day, you need also to group by a date field (date_file?): https://stackoverflow.com/questions/14191021/mysql-count-the-distinct-rows-per-day If you need to show all dates within that range icluding the ones you do not have data for, then the following SO question will help: https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range So, 99,99% sure that your question has already been answered here on SO. — Shadow, Oct 05 '20 at 12:55
my question is clear, i'm counting the number of errors by machine types on a date range : the result that i obtain is : the number of errors by machine on the whole date range but what i really want is the number of errors for each day on that date range — camelkos, Oct 05 '20 at 12:59

rf1234 · Answer 1 · 2020-10-05T13:13:59.827

Please try this:

SELECT  b.date_file, a.machine_type, count(DISTINCT a.ID_machine)
  FROM MACHINE a
 INNER JOIN `TRACE` b ON a.ID_MACHINE = b.MACHINE_ID_machine
 WHERE b.date_file BETWEEN '2020-10-01' and '2020-10-02'
   AND b.trace_status = 'OK'
 GROUP BY b.date_file, a.machine_type

Of course you can change the sequence of date and machine_type. That will give you a different grouping with a leading machine_type:

SELECT  a.machine_type, b.date_file, count(DISTINCT a.ID_machine)
  FROM MACHINE a
 INNER JOIN `TRACE` b ON a.ID_MACHINE = b.MACHINE_ID_machine
 WHERE b.date_file BETWEEN '2020-10-01' and '2020-10-02'
   AND b.trace_status = 'OK'
 GROUP BY a.machine_type, b.date_file

Assuming one individidual machine can have multiple errors per day and you wanted to count all of those errors by machine type which are recorded in table TRACE you would need to change the query like this:

SELECT  b.date_file, a.machine_type, count(b.MACHINE_ID_machine)
  FROM MACHINE a
 INNER JOIN `TRACE` b ON a.ID_MACHINE = b.MACHINE_ID_machine
 WHERE b.date_file BETWEEN '2020-10-01' and '2020-10-02'
   AND b.trace_status = 'OK'
 GROUP BY b.date_file, a.machine_type

ok, can you accept the answer then and / or vote it up please. Thanks. I have just made an update which may be helpful as well. — rf1234, Oct 05 '20 at 13:14

Show SQL grouped data for each day in a date range

1 Answers1