How to include dates even if there is no data

Asked

Viewed 72 times

1

I need to make an appointment at the bank where I count how many vehicles passed in a date range.

The code I’m using is this: 

select date(data),
count(if(axlNumber = 2, 1, null)) as eixos2,
count(if(axlNumber = 3, 1, null)) as eixos3,
count(if(axlNumber = 4, 1, null)) as eixos4,
count(if(axlNumber = 5, 1, null)) as eixos5,
count(if(axlNumber = 6, 1, null)) as eixos6,
count(if(axlNumber = 7, 1, null)) as eixos7,
count(if(axlNumber = 8, 1, null)) as eixos8,
count(if(axlNumber = 9, 1, null)) as eixos9 
from tb_vbv where date(data) between '2018/11/01' and '2019/02/28' and siteid = 20110 group by 1;

But it returns me only dates that contain data, I need you to return all dates and 0 where there is no data.

mysql sql
  • 1

    From what I understand you want all the dates of the interval regardless of whether they exist in the database. See this implementation of the generate_series function, existing in other DBMS, in Mysql: https://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql and make an Outer Join with your table.

  • that’s right! thank you!!

2 answers

3


Try it this way:

SELECT      V.DataV
        ,   IFNULL(COUNT(IF(axlNumber = 2, 1, NULL)), 0) AS eixos2
        ,   IFNULL(COUNT(IF(axlNumber = 3, 1, NULL)), 0) AS eixos3
        ,   IFNULL(COUNT(IF(axlNumber = 4, 1, NULL)), 0) AS eixos4
        ,   IFNULL(COUNT(IF(axlNumber = 5, 1, NULL)), 0) AS eixos5
        ,   IFNULL(COUNT(IF(axlNumber = 6, 1, NULL)), 0) AS eixos6
        ,   IFNULL(COUNT(IF(axlNumber = 7, 1, NULL)), 0) AS eixos7
        ,   IFNULL(COUNT(IF(axlNumber = 8, 1, NULL)), 0) AS eixos8
        ,   IFNULL(COUNT(IF(axlNumber = 9, 1, NULL)), 0) AS eixos9 
FROM    (
            SELECT  ADDDATE('1970-01-01', t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS DataV
            FROM        (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0
                    ,   (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1
                    ,   (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
                    ,   (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
                    ,   (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
        ) V
LEFT JOIN   tb_vbv ON DATE(tb_vbv.data) = V.DataV
WHERE       V.DataV BETWEEN '2018/11/01' AND '2019/02/28' 
        AND tb_vbv.siteid = 20110
GROUP BY    1;

  • Thanks for the answer! Here you are saying that the date column is ambiguous

  • Edited Answer! Corrected Query.

0

For null fields I use this way WHERE c.id_modelo_produto IS NULL OR c.id_modelo_produto IS NOT NULL ORDER BY c.id_componente DESC

  • Got it! Thanks for the answer! But could you tell me how to put this in my code? Because the columns I’m looking for are functions and not table columns

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.