cocksparrer cocksparrer - 7 months ago 17
SQL Question

MySQL SUM Column From Date Ranges

How to sum column from each date ranges.

I need to sum the price column & discount column then resulting them based on date rows.

In this case I've selected the order_id for 39 and please see the table below.

Here is the orders table I have:

id order_id price price_disc date_transaction
1 39 537 25 2014-03-28 11:44:40
2 39 323 0 2014-03-28 16:12:42
3 38 309 11 2014-03-29 15:47:48
4 39 223 16 2014-03-29 19:22:28
5 37 304 19 2014-03-30 23:31:34
6 38 109 7 2014-03-31 09:12:16
7 38 223 0 2014-03-31 17:00:35
8 38 116 9 2014-04-01 20:32:27
9 38 296 0 2014-04-02 16:45:00
10 39 482 20 2014-04-03 12:42:03
11 39 371 12 2014-04-03 20:02:10
12 36 293 13 2014-04-03 21:09:45
13 39 299 0 2014-04-03 23:28:10
14 39 403 22 2014-04-04 08:06:13


I want the result like this:

date_transaction total_price total_price_disc
2014-03-28 860 25
2014-03-29 223 16
2014-03-30 0 0
2014-03-31 0 0
2014-03-01 0 0
2014-03-02 0 0
2014-03-03 1152 32
2014-03-04 403 22
2014-03-05 0 0
2014-03-06 0 0


And this is what I've tried but only takes result for date_transaction & total_price only.

SELECT
DISTINCT DATE_FORMAT(date_transaction, '%Y-%m-%d') AS date_transaction,
COALESCE(SUM(price), 0) AS total_price
FROM orders
WHERE order_id = 39
AND date_transaction BETWEEN '2014-03-28' AND '2014-04-06' + INTERVAL 1 DAY
GROUP BY DATE_FORMAT(date_transaction, '%Y-%m-%d')
ORDER BY date_transaction ASC;

Answer

Use the DATE() function for a date.

SELECT
DISTINCT DATE(date_transaction) AS date_transaction,
SUM(price) AS total_price,
SUM(price_disc) AS total_price_disc
FROM orders
WHERE order_id = 39
AND DATE(date_transaction) BETWEEN '2014-03-28' AND '2014-04-06' + INTERVAL 1 DAY
GROUP BY DATE(date_transaction)
ORDER BY DATE(date_transaction) ASC;