Selecting sum for each day for a month

Question

I'm attempting to graph the sum for the amount of orders for each day.

SELECT DATE(FROM_UNIXTIME(timestamp)) AS timey, 
SUM(amount) AS cash 
FROM orders 
WHERE touid = :uid AND status = 1 
GROUP BY DATE(FROM_UNIXTIME(timestamp))

Works fine, except that where the sum = 0, it doesn't show in the array. I've had to manually splice the output to limit it by 31 results for the month.

I've seen the use of date_format and using the "%m" attribute, but haven't had any luck with figuring it out.

Edit:

Array ( 
    [0] => Array ( [timey] => 2015-03-22 [cash] => 0.03 ) 
    [1] => Array ( [timey] => 2015-03-23 [cash] => 0.2 ) 
    [2] => Array ( [timey] => 2015-03-29 [cash] => 0.08 ) 
    [3] => Array ( [timey] => 2015-04-03 [cash] => 0.03 ) 
    [4] => Array ( [timey] => 2015-04-04 [cash] => 0.99 ) 
    [5] => Array ( [timey] => 2015-04-06 [cash] => 1.55 ) 
    [6] => Array ( [timey] => 2015-04-07 [cash] => 0.03 ) 
    [7] => Array ( [timey] => 2015-04-10 [cash] => 4 ) 
    [8] => Array ( [timey] => 2015-04-13 [cash] => 5 ) 
    [9] => Array ( [timey] => 2015-04-14 [cash] => 8 ) 
    [10] => Array ( [timey] => 2015-04-17 [cash] => 1 ) 
    [11] => Array ( [timey] => 2015-04-18 [cash] => 4 ) 
    [12] => Array ( [timey] => 2015-05-01 [cash] => 5 ) 
    [13] => Array ( [timey] => 2015-05-02 [cash] => 2 ) 
    [14] => Array ( [timey] => 2015-05-03 [cash] => 1 ) 
    [15] => Array ( [timey] => 2015-05-05 [cash] => 3 ) 
    [16] => Array ( [timey] => 2015-05-06 [cash] => 1 ) 
    [17] => Array ( [timey] => 2015-05-07 [cash] => 12 ) 
    [18] => Array ( [timey] => 2015-05-08 [cash] => 1 ) 
    [19] => Array ( [timey] => 2015-05-11 [cash] => 0.1 ) 
    [20] => Array ( [timey] => 2015-05-13 [cash] => 1 ) 
    [21] => Array ( [timey] => 2015-05-14 [cash] => 1.9 ) 
    [22] => Array ( [timey] => 2015-05-16 [cash] => 2 ) 
    [23] => Array ( [timey] => 2015-05-17 [cash] => 0.15 ) 
    [24] => Array ( [timey] => 2015-05-18 [cash] => 1 ) 
    [25] => Array ( [timey] => 2015-05-19 [cash] => 1.09 ) 
    [26] => Array ( [timey] => 2015-05-23 [cash] => 0.5 ) 
    [27] => Array ( [timey] => 2015-05-24 [cash] => 2.5 ) 
    [28] => Array ( [timey] => 2015-05-26 [cash] => 3 ) 
    [29] => Array ( [timey] => 2015-05-27 [cash] => 2 ) 
    [30] => Array ( [timey] => 2015-05-28 [cash] => 4 ) 
)

Output after i've sliced it in PHP.

See http://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates for how to fill in a table with all the dates in the month. Then do a `LEFT JOIN` from this table to your `orders` table. — Barmar, Jun 08 '15 at 22:04

Drew · Answer 1 · 2015-06-09T15:05:20.247

Edit: ok try this concept, shamelessly taken from here. Tweak your names and clauses accordingly but you get the concept.

CREATE TABLE orders
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_date DATETIME,
  product_id INT,
  quantity INT,
  customer_id INT
);
INSERT INTO orders (order_date, product_id, quantity, customer_id)
  VALUES
  ('2009-08-15 12:20:20', '1', '2', '123'),
  ('2009-08-15 12:20:20', '2', '2', '123'),
  ('2009-08-17 16:43:09', '1', '1', '456'),
  ('2009-08-18 09:21:43', '1', '5', '789'),
  ('2009-08-18 14:23:11', '3', '7', '123'),
  ('2009-08-21 08:34:21', '1', '1', '456');

.

CREATE TABLE aCalendar (datefield DATE);

.

DELIMITER |
CREATE PROCEDURE fill_calendar(start_date DATE, end_date DATE)
BEGIN
  DECLARE crt_date DATE;
  SET crt_date=start_date;
  WHILE crt_date < end_date DO
    INSERT INTO aCalendar VALUES(crt_date);
    SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY);
  END WHILE;
END |
DELIMITER ;

cram calendar days in for many years:

CALL fill_calendar('2007-01-01', '2009-12-31');
CALL fill_calendar('2010-01-01', '2017-12-31'); -- coffee break, 1 minute
-- select count(*) from aCalendar;

-- an index on aCalendar wouldn't be a bad idea

sales for Aug2009:

SELECT aCalendar.datefield AS theDate,
       IFNULL(SUM(orders.quantity),0) AS total_sales
FROM orders RIGHT JOIN aCalendar ON (DATE(orders.order_date) = aCalendar.datefield)
WHERE (aCalendar.datefield BETWEEN DATE('2009-08-01') AND DATE('2009-08-31'))
GROUP BY theDate
ORDER BY theDate

theDate     total_sales  
2009-08-01  0            
2009-08-02  0            
2009-08-03  0            
2009-08-04  0            
2009-08-05  0            
2009-08-06  0            
2009-08-07  0            
2009-08-08  0            
2009-08-09  0            
2009-08-10  0            
2009-08-11  0            
2009-08-12  0            
2009-08-13  0            
2009-08-14  0            
2009-08-15  4            
2009-08-16  0            
2009-08-17  1            
2009-08-18  12           
2009-08-19  0            
2009-08-20  0            
2009-08-21  1            
2009-08-22  0            
2009-08-23  0            
2009-08-24  0            
2009-08-25  0            
2009-08-26  0            
2009-08-27  0            
2009-08-28  0            
2009-08-29  0            
2009-08-30  0            
2009-08-31  0

This doesn't fix the problem. There's no NULL value for dates that don't exist in the table. — Barmar, Jun 08 '15 at 22:01
As mentioned my barmar, it doesn't output null values. I've already explored this. Appreciate the insight in how I can utilize `ifnull` in the future. — , Jun 08 '15 at 22:03
Unfortunately, this isn't an able solution as I can't have any excess orders unless it's actually placed. — , Jun 09 '15 at 14:46
i was joking about that part. the above solves it, no fake orders. the point is that you have to synthesize nulls, like the above solution does — Drew, Jun 09 '15 at 14:57
I'm using the unix timestamp, would I have to convert the dates into that or vice versa? — , Jun 09 '15 at 20:47
can u try the above concept with your datatype. if it bombs i will try to fix it. — Drew, Jun 09 '15 at 20:51
Mysql appears to be wanting more input from the procedure. `MariaDB [(none)]> CALL fill_calendar('2015-01-01', '2017-12-31'); -> \c MariaDB [(none)]> ` — , Jun 09 '15 at 20:56
we may have to go into chat if this thread goes too long. can u keep Date datatype for the aCalendar table and parameters to the proc to begin with. Maria should be compatible, no ? — Drew, Jun 09 '15 at 21:01
I don't have the reputation to be able to chat. It require `20 reputation` — , Jun 09 '15 at 21:08
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/80110/discussion-between-sladey-and-drew-pierce). — , Jun 09 '15 at 21:12

Selecting sum for each day for a month

1 Answers1