Calculate daily sales in MySQL

Question

I have a simple table that shows the date and total sales for a specific 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:

• daily sale
• average sales
• % growth

The result should look like (manually calculated, or 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 possible in the sql statement, or should I calculate using PHP or other server software?

Answer 1

Here is a complete query without a subquery on each line: (Thanks to @nmarsh for writing the hardest part)

See SQL script: 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;

Source 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

Answer 2

, , , :

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

, . CASE, ( )

, - , , GROUP BY SUM .

Answer 3

. ( ) .

PHP, , MySQL, /.

Answer 4

, , .

ROW_NUMBER(), 2 ROW_NUMBER() ROW_NUMBER() - 1, . :

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 

MSSQL, MySQL ROW_NUMBER OVER.

Answer 5

,

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'

id

---

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

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       

, datetime ask

sorry for my bad english

change the last 3 lines are commented because I used it only for test