calculate sales per day in MySQL

Question

I have a simple table which holds the date and the total sales made for a certain group:

date       | totalsales
=======================
2014-05-01 | 3000
2014-05-02 | 3100
2014-05-03 | 3500
2014-05-04 | 3650

I like to calculate some things like:

sales per day
average sales
growth in %

Result should look like (calculate by hand so maybe wrong :) )

date       | sales  | average | growth
=======================================
2014-05-01 |   0    |  0      |   0
2014-05-02 | 100    | 50      | 100
2014-05-03 | 400    | 166.66  | 400
2014-05-04 | 150    | 162.5   |  37.5

Is this even possible in a sql statement or should I calculate with PHP or another server software?

you can do it with mysql https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html — user468891, May 11 '14 at 19:48
Comment by user468891 refers to MySQL Group by statements and aggregations. As noted by Anthony Raymond, this is not a simple group by. To get these calcs you need to reference the previous record in the data set. That is lost in a normal group by. This can me completed by creating an off-set data-set using ROW_NUMBER. Sample posted below. — Richard Vivian, May 11 '14 at 21:03
If you need a huge query that you don't understand, don't use it. Calculate it outside the db. The result set isn't much (if any) bigger. — Rudie, May 11 '14 at 21:08

score 3 · Answer 1 · answered May 11 '14 at 21:04

Assuming each date gets its own unique row, you could do it by joining back to your original table like so:

SELECT t1.Date, CASE WHEN t2.Date IS NULL THEN 0 ELSE (t1.totalsales - t2.totalsales)
END AS sales
FROM table t1
LEFT JOIN table t2 ON t2.Date = DATE_ADD(t1.Date, INTERVAL -1 DAY)
ORDER BY 1

This will give you at least your first column, and you should be able to figure out the math for the rest from there. It's important to use a left join with the CASE statement here because otherwise you won't get the lowest date in your table (your first row)

If each date does not get its own unique row, this is method is still viable, you just need to create your datasets in a subquery using GROUP BY and SUM on the date column.

The method above does not always work. This assumes that the previous record was for the previous day. If you have weekends with no data the Day-1 has no entry . I.e you join to NULL. You can overcome this limitation by giving each row a unique reference with ROW_NUMBER() function. — Richard Vivian, May 12 '14 at 11:04

score 2 · Accepted Answer · answered May 11 '14 at 23:09

Here is the full query with no subselect at each row : (Thanks to @nmarsh for writting the hardest part)

See SQL fiddle : http://sqlfiddle.com/#!2/be4654/34/0

SELECT 
  t1.Date,
  CASE
    WHEN t2.date IS NULL THEN 0 ELSE (t1.totalSales - t2.totalSales)
    END AS sales,
  CASE 
    WHEN t2.date IS NULL THEN 0 / (@curRow := @curRow + 1) ELSE ((@curSum := @curSum + (t1.totalSales - t2.totalSales)) / (@curRow := @curRow + 1))
    END AS average,
  CASE
    WHEN t3.date IS NULL AND t2.date IS NULL THEN 0
    WHEN t3.date IS NULL THEN (t1.totalSales - t2.totalSales)
    WHEN t2.date IS NULL THEN 0 ELSE ((t1.totalSales - t2.totalSales) * 100) / (t2.totalSales - t3.totalSales)
    END AS growth
FROM test t1
LEFT JOIN test t2 ON t2.date = DATE_ADD(t1.Date, INTERVAL -1 DAY)
LEFT JOIN test t3 ON t3.date = DATE_ADD(t2.Date, INTERVAL -1 DAY)
JOIN (SELECT @curRow := 0) r
JOIN (SELECT @curSum := 0) ct
ORDER BY 1;

Original table :

date       | totalsales
=======================
2014-05-01 |   3000
2014-05-02 |   3100
2014-05-03 |   3500
2014-05-04 |   3650

OUTPUT

date       | sales  | average | growth
=======================================
2014-05-01 |   0    |  0      | 0
2014-05-02 | 100    | 50      | 100
2014-05-03 | 400    | 166.66  | 400
2014-05-04 | 150    | 162.5   | 37.5

score 0 · Answer 3 · edited May 23 '17 at 12:27

0

You can use recursive statement. In each iteration, calculate the requested data for one day, and drop the first (oldest) day.

You can also do it with PHP, which seems better because you don't want to put too much load on the MySQL tables in case it does not saves you time/calculations.

edited May 23 '17 at 12:27

Community

1
1

answered May 11 '14 at 20:38

Itay Gal

10,706
6
36
75

I have found that you generally get the best performance when you can use a db set based query and good indexes. When you start looping, you sometimes get performance issues. Especially if you have to make a DB call in the loop. If you can get everything in memory, then loop your performance may be OK. – Richard Vivian May 11 '14 at 20:59

score 0 · Answer 4 · answered May 11 '14 at 20:53

Not too sure about the numbers, but if you are more specific about the results I can double check.

You can use ROW_NUMBER() to create 2 data sets and join them on ROW_NUMBER() and ROW_NUMBER()-1 to get an offset to current and previous values to calculate the growth. Sample :

DECLARE @Data TABLE (SalesDate DATETIME, totalSales INT)

INSERT INTO @Data (SalesDate , totalSales) VALUES ('2014-05-01' , 3000)
INSERT INTO @Data (SalesDate , totalSales) VALUES ('2014-05-02' , 3100)
INSERT INTO @Data (SalesDate , totalSales) VALUES ('2014-05-03' , 3500)
INSERT INTO @Data (SalesDate , totalSales) VALUES ('2014-05-04' , 3650)


SELECT  
      CurrentDt.SalesDate 
     ,ISNULL(CurrentDt.totalSales - PreviousDt.totalSales ,0)                       AS  Sales
     ,FirstDate.FirstDate
     , NULLIF(CAST((CurrentDt.SalesDate - FirstDate.FirstDate) AS INT)+1,0)         AS SellingDays
     ,(ISNULL(CurrentDt.totalSales - PreviousDt.totalSales ,0)) 
     / NULLIF(CAST((CurrentDt.SalesDate - FirstDate.FirstDate) AS INT)+1,0)         AS  AverageSales
FROM

    (SELECT Min(SalesDate) AS FirstDate FROM @Data) AS FirstDate,
    /*Base Sales Data*/
    (
    SELECT
         ROW_NUMBER() OVER(ORDER BY SalesDate) AS RowNum
        ,SalesDate 
        ,totalSales
    FROM
        @Data 
    ) AS CurrentDt

    /*Previous Value for Growth*/
    LEFT JOIN 
    (
    SELECT 
         ROW_NUMBER() OVER(ORDER BY SalesDate) AS RowNum
        ,SalesDate 
        ,totalSales  
    FROM
        @Data 
    ) AS PreviousDt
        ON CurrentDt.RowNum  -1 = PreviousDt.RowNum

I have used MSSQL, but MySQL supports ROW_NUMBER OVER.

score 0 · Answer 5 · answered May 11 '14 at 22:21

I hope this query help you

SELECT 
    sample.id,
    sample.date AS oggi,
    sample.value AS sales,
    ((SELECT SUM(sample.value) FROM sample WHERE  sample.date <= oggi    ) / (SELECT COUNT(sample.value) FROM sample WHERE  sample.date <= oggi    ) ) AS avarege,  
    sample.value / IF((SELECT sample.value FROM sample WHERE  sample.date =  (oggi - INTERVAL 1 DAY )) = 0,sample.value,(SELECT sample.value FROM sample WHERE  sample.date =  (oggi - INTERVAL 1 DAY ))) *100 AS 'growt-percent'
    -- (SELECT SUM(sample.value) FROM sample WHERE  sample.date <= oggi    ) AS somma,
    -- (SELECT count(sample.value) FROM sample WHERE  sample.date <= oggi    ) AS conta,
    -- (SELECT sample.value FROM sample WHERE  sample.date =  (oggi - INTERVAL 1 DAY )) as valoreieri,
FROM sample 
WHERE sample.date BETWEEN '2014-05-01 00:00:00' AND '2014-05-31 00:00:00'

table data is

id date value

1  2014-05-01 00:00:00         0
2  2014-05-02 00:00:00       100
3  2014-05-03 00:00:00       400
4  2014-05-04 00:00:00       150
5  2014-05-05 00:00:00       200

result is

id oggi sales avarege growt-percent

1  2014-05-01 00:00:00       0  0.0000    (NULL)         
2  2014-05-02 00:00:00     100  50.0000   100.0000       
3  2014-05-03 00:00:00     400  166.6667  400.0000       
4  2014-05-04 00:00:00     150  162.5000  37.5000        
5  2014-05-05 00:00:00     200  170.0000  133.3333

note that i use datetime field not only date if you have question about query ask

sorry for my bad english

edit the last 3 rows are commented because i used it only for test

calculate sales per day in MySQL

5 Answers5