Find cumulative sum of insurance sales amount with increasing date while considering unique salesman names count

user_15358022 0
Viewed 47

I have the following table

+------------+----------+------------+
| SaleDate   | SaleName | SaleAmount |
+------------+----------+------------+
| 2021-03-01 | Jack     |         40 |
| 2021-03-02 | Mark     |         60 |
| 2021-03-03 | Sam      |         30 |
| 2021-03-03 | Mark     |         70 |
| 2021-03-02 | Sam      |        100 |
| 2021-03-01 | Jack     |         70 |
+------------+----------+------------+

I want to find the date-wise cumulative SalesAmount, while also maintaining the increasing count of salesman names that come into the table.

+------------+-----------------+-----------------+
| SaleDate   | Count(SaleName) | Sum(SaleAmount) |
+------------+-----------------+-----------------+
| 2021-03-01 | 1               |              110|      
| 2021-03-02 | 3               |              270|
| 2021-03-03 | 3               |              370|
+------------+-----------------+-----------------+

So basically the task is to eliminate the duplicate names, that may occur on the daily basis, or in a cumulative fashion?

sql
2 Answers

You can use a window function to get the first date a sales person appears. Then use aggregation and a cumulative sum:

select saledate,
       sum(sum(case when seqnum = 1 then 1 else 0 end)) over (order by saledate),
       sum(sum(amount)) over (order by saledate) as running_amount
from (select t.*,
             row_number() over (partition by salename order by saledate) as seqnum
      from t
     ) t
group by saledate
order by saledate;
user_11440350

Although Gordon has already answered the question, here's another solution, without the use of any window functions

WITH row_data_1 AS(
SELECT SaleDate,SaleName,SUM(SaleAmount) AS SaleAmount FROM INSURANCE GROUP BY SaleDate,SaleName
),
row_data_2 AS(
SELECT t1.SaleDate,
COUNT(CASE when t2.SaleName IS NULL THEN 1 END) AS cnt,
SUM(t1.SaleAmount) AS amt
FROM row_data_1 t1
LEFT JOIN row_data_1 t2
ON t2.SaleName = t1.SaleName AND
t2.SaleDate < t1.SaleDate
GROUP BY t1.SaleDate
)
SELECT
SaleDate,
SUM(cnt) OVER (ORDER BY SaleDate) AS NumSalesman,
SUM(amt) OVER (ORDER BY SaleDate) AS TotalAmount
FROM row_data_2
ORDER BY SaleDate;
user_153580220
Related