#1
  1. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052

    select 0 for the day that do not have a value


    Hi

    I want to find the daily sales for the current month. my following query works however
    it does not return the days that do not have any sales. whereas i want to display '0' for such days

    Code:
    SELECT DATE_FORMAT(orderDate,'%d') as DAY, 
                    SUM(totalOrderAmount) as sales FROM orders 
    WHERE year(curdate()) = year(orderDate) 
    AND month(curdate()) = month(orderDate) 
    GROUP BY DAY 
    ORDER BY DAY

    I tried using IFNULL on DAY and SUM but get the same result. I want to avoid created a separate table where it
    has range of days for each month and then using a JOIN on it. is it possible by some DATE function/trick?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,664
    Rep Power
    4288
    you need a numbers table... see Loop for seqential records with dates

    once you have it installed, do a SHOW CREATE TABLE and then i can show you how to write your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052
    Code:
    CREATE TABLE `numbers` (
     `n` int(11) NOT NULL,
     PRIMARY KEY (`n`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  6. #4
  7. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052
    I have added 31 numbers
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,664
    Rep Power
    4288
    make sure that 0 is one of the numbers in your numbers table

    run this to see how to get the first date of the current month --
    Code:
    SELECT CURRENT_DATE - INTERVAL DAY(CURRENT_DATE)-1 DAY   AS first_date_of_current_month
    then run this to see how to generate all the dates of the current month --
    Code:
    SELECT CURRENT_DATE - INTERVAL DAY(CURRENT_DATE)-1 DAY
                        + INTERVAL n DAY                     AS all_dates_of_current_month
      FROM numbers
     WHERE n BETWEEN 0 AND DAY(LAST_DAY(CURRENT_DATE))-1
    now you can use this as the left table in your outer join --
    Code:
    SELECT m.the_day
         , SUM(o.totalOrderAmount) AS sales 
      FROM ( SELECT CURRENT_DATE - INTERVAL DAY(CURRENT_DATE)-1 DAY
                                 + INTERVAL n DAY  AS the day
               FROM numbers
              WHERE n BETWEEN 0 AND DAY(LAST_DAY(CURRENT_DATE))-1  
           ) AS m
    LEFT OUTER
      JOIN orders AS o
        ON o.orderDate = m.the_day
    GROUP
        BY the_day
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052
    it gives me NULL for all "sales"

    my orderDate is DATETIME, so I even tried by the following query, but still gives NULL for all "sales"

    Code:
    SELECT m.the_day
         , SUM(o.totalOrderAmount) AS sales,DATE_FORMAT(o.orderDate,'%Y-%m-%d') as orderDate 
      FROM ( SELECT CURRENT_DATE - INTERVAL DAY(CURRENT_DATE)-1 DAY
                                 + INTERVAL n DAY  AS the_day
               FROM numbers
              WHERE n BETWEEN 0 AND DAY(LAST_DAY(CURRENT_DATE))-1  
           ) AS m
    LEFT OUTER
      JOIN orders AS o
        ON o.orderDate = m.the_day
    GROUP
        BY the_day
    ps: I have 0 in numbers table
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,617
    Rep Power
    1766
    Things like this make my eyes bleed - but ... you specifically use a DATE_FORMAT to get a DATE format column, then seem to ignore it in your JOIN syntax. Also, just for my sanity (if nothing else) could you give it a different alias to make it wholly clear it's a different column from the one in the table, maybe DateOfOrder?
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,664
    Rep Power
    4288
    in order to diagnose your data problems, please run this --
    Code:
    SELECT m.the_day
         , MAX(o.totalOrderAmount)
      FROM ( SELECT CURRENT_DATE - INTERVAL DAY(CURRENT_DATE)-1 DAY
                                 + INTERVAL n DAY  AS the_day
               FROM numbers
              WHERE n BETWEEN 0 AND DAY(LAST_DAY(CURRENT_DATE))-1  
           ) AS m
    LEFT OUTER
      JOIN orders AS o
        ON o.orderDate = m.the_day
    GROUP
        BY the_day
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052
    it gives me NULL in the amount column for all days

    Code:
    2016-09-01 	NULL
    2016-09-02 	NULL
    2016-09-03 	NULL
    2016-09-04 	NULL
    2016-09-05 	NULL
    ......

    @simons: naming variable is just a coding style, differs programmer to programmer , on the other part, I used DATE_FORMAT because
    i wanted to selected date in the form of YEAR-MONTH-DAY. otherwise it returns seconds etc too. now, I am thinking if it would make any difference
  18. #10
  19. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052
    fixed the issue. the problem was same that my orderDate is DATETIME. However,
    only an expert like you might tell me, my usage of DATE_FORMAT() in select
    was not making any difference. I used DATE_FORMAT() directly in ON and it worked
    Code:
    SELECT m.the_day
         , SUM(o.totalOrderAmount) AS sales
      FROM ( SELECT CURRENT_DATE - INTERVAL DAY(CURRENT_DATE)-1 DAY
                                 + INTERVAL n DAY  AS the_day
               FROM numbers
              WHERE n BETWEEN 0 AND DAY(LAST_DAY(CURRENT_DATE))-1  
           ) AS m
    LEFT OUTER
      JOIN orders AS o
        ON DATE_FORMAT(o.orderDate,'%Y-%m-%d') = m.the_day
    GROUP
        BY the_day
    Last edited by jojoba; September 19th, 2016 at 02:43 PM.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,617
    Rep Power
    1766
    The columns you JOIN ON would need to be the same for a) any indexing to work and, most importantly, any match being found - in this case you seemed to be trying to (as an example) have 2016-09-19 23:35 equal 2016-09-19 and they are just not the same. The column the_day was just that, a day without any time component, however your orderDate was a DATETIEM, whoch does have a time component (even if you were not making use of it).

    Comments on this post

    • jojoba agrees
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,664
    Rep Power
    4288
    in the ON clause, change your DATE_FORMAT() to DATE()

    smooove ;o)

    Comments on this post

    • jojoba agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Location
    Her heart... she claims!
    Posts
    1,703
    Rep Power
    1052
    thank you Simon for the explanation. I made orderDate as DATETIME because in other features I would
    need to pull out data based on HOURS etc. This query is meant to pull out DAILY data that is why I am interested in date without time.
    I will keep your guideline in head in future queries

    Thank you Rudy, you are great

IMN logo majestic logo threadwatch logo seochat tools logo