CODEWITHSUNDEEP
mysql
tlano
tlano

Reputation: 45

MySQL Sum values by date, compare this year to last year

Let's say I have a table full of orders. There can be multiple orders per day, with different total amounts:

# Create orders table
CREATE TABLE orders (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `total` decimal(9,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

I want to create a bunch of random test data for this table, so I created a stored procedure:

# Create test data for orders table
DELIMITER $$
CREATE PROCEDURE prepare_orders_data()

BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE j INT DEFAULT FLOOR(RAND() * 10 + 1); # Number of order entries in one day
                                                # randomized between 1 and 10 days

  # Create 2 years worth of data from 2014-01-01 to 2015-12-31
  WHILE i < (365*2) DO
    WHILE j > 0 DO
      INSERT INTO orders(order_date, total) 
      VALUES (DATE_ADD('2014-01-01', INTERVAL 1*i DAY), RAND() * 100);

      SET j = j - 1;
    END WHILE;

    SET j = FLOOR(RAND() * 10 + 1); #Random number of days from 1-10
    SET i = i + 1;
  END WHILE;

END$$
DELIMITER ;

CALL prepare_orders_tlano_data();

Now that I have data, what I want to do is to get the sum of the order totals by date, and compare them to the sum of the order totals from the previous year. The previous year defined as exactly 52 weeks before the current date (I want to look at dates that are determined by the day of the week, such as: Thanksgiving, Black Friday, Easter, etc. Most of the time, the dates that will be compared are the one day off (Eg: 2015-04-08 is compared to 2015-09-08).

I've already figured out a solution, but I don't believe that it is optimal. This is because I'm basically joining the same table on itself, and I'm thinking there is a quicker way to do this. Here is that solution:

# Get totals for dates and dates from 52 weeks previous 
# (same weekday approximately 1 year in the past)
SELECT
    DATE(thisYear.order_date) AS ThisYearOrderDate,
    DATE(lastYear.order_date) AS LastYearOrderDate,
    YEAR(thisYear.order_date) as ThisYear,
    YEAR(lastYear.order_date) as LastYear,
    DAYNAME(thisYear.order_date) AS DayName,
    SUM(thisYear.total) AS ThisYearTotal,
    lastYear.total AS LastYearTotal
FROM orders thisYear 
    INNER JOIN (
        SELECT 
            order_date as order_date,
            SUM(total) as total
        FROM orders
        GROUP BY order_date
    ) lastYear
ON DATE_ADD(thisYear.order_date, INTERVAL -52 WEEK) = lastYear.order_date
GROUP BY thisYear.order_date;

And here is an example of what it would return (results will vary due to randomized data in stored procedure):

+--------------+--------------+----------+---------------+---------------+
| ThisYearDate | LastYearDate | DayName  | ThisYearTotal | LastYearTotal |
+--------------+--------------+----------+---------------+---------------+
| 2015-01-01   | 2014-01-02   | Thursday | 363.56        | 11.26         |
| 2015-01-02   | 2014-01-03   | Friday   | 137.62        | 189.76        |
| 2015-01-03   | 2014-01-04   | Saturday | 399.40        | 257.42        |
| 2015-01-04   | 2014-01-05   | Sunday   | 502.80        | 336.38        |
| 2015-01-05   | 2014-01-06   | Monday   | 107.59        | 466.79        |
+--------------+--------------+----------+---------------+---------------+

Can anybody think of a different way to accomplish this?

EDIT:
I took a look at @Used_By_Already 's solution and rewrote it a bit to give it the same output as my table:

SELECT
    (CASE WHEN order_date < '2014-12-31'
         THEN DATE_ADD(order_date, INTERVAL 52 WEEK)
         ELSE order_date END) as ThisYearOrderDate,
    (CASE WHEN order_date < '2014-12-31'
         THEN order_date
         ELSE DATE_ADD(order_date, INTERVAL -52 WEEK) END) as LastYearOrderDate,
     DAYNAME(order_date) DayName,
     SUM(CASE WHEN order_date >= '2014-12-31'
         THEN total ELSE 0 END) as ThisYearTotal,
     SUM(CASE WHEN order_date < '2014-12-31'
         THEN total ELSE 0 END) as LastYearTotal
FROM orders
GROUP BY ThisYearOrderDate;

This works well and runs faster than my solution. The only concerns I have are that it requires these date filters and it will only work in a one-year range as any overlap between ThisYearOrderDate and LastYearOrderDate will lead to some misleading entries:

+--------------+--------------+-----------+---------------+---------------+
| ThisYearDate | LastYearDate | DayName   | ThisYearTotal | LastYearTotal |
+--------------+--------------+-----------+---------------+---------------+
| 2014-12-31   | 2014-01-01   | Wednesday | 18.01         | 253.56        |
| 2015-01-01   | 2014-01-02   | Thursday  | 363.56        | 11.26         |
| ...          | ...          | ...       | ...           | ...           |
| 2015-12-30   | 2014-12-31   | Wednesday | 380.71        | 0.00          |
| 2015-12-31   | 2015-01-01   | Thursday  | 400.36        | 0.00          |
+--------------+--------------+-----------+---------------+---------------+

Upvotes: 3

Views: 2111

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35553

Use a case expression on the date and group by this, and also use a similar case expressions for the two sums (conditional aggregation).

select
      case when order_date < '2016-01-01' then order_date + INTERVAL 52 WEEKS else order_date end as an_order_date_pair
    , SUM(case then order_date  < '2016-01-01' then total end) as prev_yr
    , SUM(case then order_date >= '2016-01-01' then total end) as this_yr
from orders
group by
      case when order_date < '2016-01-01' then order_date + INTERVAL 52 WEEKS else order_date end

I wasn't able to get this running under MySQL at sqlfiddle so the following was done using Postgres but you will see the syntax is extremely similar fo rthis need:

SQL Fiddle PostgreSQL 9.3 Schema Setup:

CREATE TABLE Orders
    (order_date timestamp , total decimal(12,3))
;

INSERT INTO Orders
    (order_date, total)
VALUES
    ('2014-01-02 00:00:00', 11.26),
    ('2014-01-03 00:00:00', 189.76),
    ('2014-01-04 00:00:00', 257.42),
    ('2014-01-05 00:00:00', 336.38),
    ('2014-01-06 00:00:00', 466.79),
    ('2015-01-01 00:00:00', 363.56),
    ('2015-01-02 00:00:00', 137.62),
    ('2015-01-03 00:00:00', 399.40),
    ('2015-01-04 00:00:00', 502.80),
    ('2015-01-05 00:00:00', 107.59)
;

Query 1:

select
      case when order_date < '2015-01-01'
          then order_date + INTERVAL '52 WEEKS'
          else order_date
      end as an_order_date_pair
    , SUM(case when order_date < '2015-01-01'
          then total else 0
       end) as prev_yr
    , SUM(case when order_date >= '2015-01-01'
          then total else 0
       end) as this_yr
from orders
group by
      case when order_date < '2015-01-01'
          then order_date + INTERVAL '52 WEEKS'
          else order_date
      end

Results:

|        an_order_date_pair | prev_yr | this_yr |
|---------------------------|---------|---------|
| January, 01 2015 00:00:00 |   11.26 |  363.56 |
| January, 05 2015 00:00:00 |  466.79 |  107.59 |
| January, 02 2015 00:00:00 |  189.76 |  137.62 |
| January, 04 2015 00:00:00 |  336.38 |   502.8 |
| January, 03 2015 00:00:00 |  257.42 |   399.4 |

Upvotes: 4

Related Questions