CODEWITHSUNDEEP
sqlms-access
Leroy
Leroy

Reputation: 644

Cumulative SQL calculation

I have an MS Access table (tbl_SalesData) as below:

 SCOREDATE | USERID  |      Sales     | HoursWorked | 
-----------------------------------------------------
01/03/2017 | 20511   |       20       |     10      | 
02/03/2017 | 20511   |       27       |     9       | 
03/03/2017 | 20511   |       18       |     9       | 
04/03/2017 | 20511   |       20       |     8       |

I am trying to write an SQL query that will calculate sales per hour both for each day and also for YTD.

The YTD calculation would need to add up all of the sales up to and including that day and the same for the HoursWorked and then divide total sales by total Hours Worked. So the desired output would be:

 SCOREDATE | USERID  |   Sales/Hour   | Sales/Hour YTD | 
--------------------------------------------------------
01/03/2017 | 20511   |       2        |      2         | 
02/03/2017 | 20511   |       3        |      2.47      | 
03/03/2017 | 20511   |       2        |      2.32      | 
04/03/2017 | 20511   |       2.5      |      2.36      |

I'm really struggling with this one. The daily sales/Hour is quite straightforward as below:

SELECT SCOREDATE, USERID, Sales/HoursWorked
FROM tbl_SalesData

But unfortunately I can't seem to get close to figuring out how the YTD part would work. Could someone please point me in the right direction?

Upvotes: 1

Views: 137

Answers (2)

Leroy
Leroy

Reputation: 644

Huge thanks to Gordon Linoff for his great answer using a correlated subquery. I have also come across another solution which I thought might be useful to post here. It uses a self join rather than a subQuery and I believe should run a bit faster with large datasets

SELECT 
    tbl1.scoreDate, 
    tbl1.UserID, 
    tbl1.sales/tbl1.HoursWorked as AvgSales, 
    sum(tbl2.Sales)/sum(tbl2.Hoursworked) AS AvgSalesYTD
FROM 
    tbl_SalesData AS tbl1 
INNER JOIN 
    tbl_SalesData AS tbl2 
ON 
    tbl1.UserID = tbl2.UserID 
AND 
    tbl1.scoreDate >= tbl2.scoreDate
GROUP BY 
    tbl1.scoreDate, 
    tbl1.UserID,  
    tbl1.sales/tbl1.HoursWorked

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can use a correlated subquery:

select sd.*,
       (select sum(sd2.sales) / sum(sd2.hours_worked)
        from tbl_SalesData sd2
        where sd2.userid = sd.userid and sd2.scoredate <= sd.scoredate
       ) as running_average_sales
from tbl_SalesData as sd;

Upvotes: 3

Related Questions