CODEWITHSUNDEEP
phpmysqlsql
Xaver
Xaver

Reputation: 11652

calculate sales per day in MySQL

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:

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?

Upvotes: 4

Views: 4786

Answers (5)

Anthony Raymond
Anthony Raymond

Reputation: 7862

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

Upvotes: 2

ciro
ciro

Reputation: 801

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

Upvotes: 0

nmarsh
nmarsh

Reputation: 164

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.

Upvotes: 3

Richard Vivian
Richard Vivian

Reputation: 1750

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.

Upvotes: 0

Itay Gal
Itay Gal

Reputation: 10834

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.

Upvotes: 0

Related Questions