1

I make a select in MySQL and search between 2 dates and make a count of elements by date and it works fine but, if a date has no records it does not appear, what I'm looking for is something like this:

Column date / Count

  • 2017-01-01 / 1
  • 2017-01-02 / 2
  • 2107-01-03 / 0 *Date without records
  • 2017-01-04 / 1

What I get

  • 2017-01-01 / 1
  • 2017-01-02 / 2
  • 2017-01-04 / 1

2017-01-03 does not show because has no records

Query:

SELECT I.FechaInstalacion, count(Case when I.ServicioId = 1 then 1 end) as P
FROM Instalacion I
WHERE I.EmpleadoId = 4 and I.FechaInstalacion>='2018-01-01' and 
I.FechaInstalacion<= '2018-01-31'
group by I.FechaInstalacion

Thank you!

ucMedia
  • 4,105
  • 4
  • 38
  • 46
Alejandro Gomez Guerrero
  • 348
  • 2
  • 3
  • 10
  • how can you get a record, which doesn't exists in table ??? – Ravi Jan 31 '18 at 18:43
  • sounds like you need another table with all dates and left join that table. – isaace Jan 31 '18 at 18:48
  • Or simply make the assumption that when a date is missing from the results that there are no rows for that date, which is true. – KIKO Software Jan 31 '18 at 18:49
  • 1
    see https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range – isaace Jan 31 '18 at 18:53
  • Possible duplicate of [MySQL: Select All Dates In a Range Even If No Records Present](https://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present) – Nick May 05 '18 at 02:16

0 Answers0