CODEWITHSUNDEEP
sqlsql-servert-sql
asmgx
asmgx

Reputation: 8004

Accumulating previous rows with grouping

I have this table on MS SQL Server

Customer    Month    Amount
-----------------------------
Tom         1        10
Kate        1        60
Ali         1        70
Tom         2        50
Kate        2        40
Tom         3        80
Ali         3        20

I want the select to get accumulation of the customer for each month

Customer    Month    Amount
-----------------------------
Tom         1        10
Kate        1        60
Ali         1        70
Tom         2        60
Kate        2        100
Ali         2        70
Tom         3        140
Kate        3        100
Ali         3        90

Noticing that Ali has no data for the month of 2 and Kate has no data for the month of 3

I have done it but the problem is that for the missing month for each customer no data shows i.e. Kate has to be in month 3 with 100 amount and Ali has to be in Month 2 with 70 amount

declare @myTable as TABLE   (Customer varchar(50), Month int, Amount int)
;

INSERT INTO @myTable
    (Customer, Month, Amount)
VALUES
    ('Tom', 1, 10),
    ('Kate', 1, 60),
    ('Ali', 1, 70),
    ('Tom', 2, 50),
    ('Kate', 2, 40),
    ('Tom', 3, 80),
    ('Ali', 3, 20);


select * from @myTable


select
    SUM(b.Amount),a.Customer, a.Month
from
    @myTable a
        inner join
    @myTable b
        on a.Customer = b.Customer and 
            a.Month >= b.Month
group by
    a.Customer, a.Month

Upvotes: 8

Views: 622

Answers (9)

paparazzo
paparazzo

Reputation: 45096

I think this does what you want 

declare @myTable as TABLE   (Customer varchar(50), Month int, Amount int);
INSERT INTO @myTable (Customer, Month, Amount)
VALUES
    ('Tom', 1, 10),
    ('Kate', 1, 60),
    ('Ali', 1, 70),
    ('Tom', 2, 50),
    ('Kate', 2, 40),
    ('Tom', 3, 80),
    ('Ali', 3, 20);

 select dts.Month, cts.Customer, isnull(t.Amount, 0) as Amount
      , sum(isnull(t.Amount, 0)) over(partition by cts.Customer order by dts.Month) as CumAmt 
  from ( select distinct customer 
                from @myTable
       ) cts 
  cross join ( select distinct Month 
                from @myTable 
             ) dts
  left join @myTable t
    on t.Customer = cts.Customer 
   and t.Month = dts.Month
 order by dts.Month, cts.Customer;

Month       Customer                                           Amount      CumAmt
----------- -------------------------------------------------- ----------- -----------
1           Ali                                                70          70
1           Kate                                               60          60
1           Tom                                                10          10
2           Ali                                                0           70
2           Kate                                               40          100
2           Tom                                                50          60
3           Ali                                                20          90
3           Kate                                               0           100
3           Tom                                                80          140

Upvotes: 1

user9326853
user9326853

Reputation:

to be clear(in answer Amount and AmountSum)

DECLARE @myTable TABLE(Customer varchar(50), Month int, Amount int);

INSERT INTO @myTable(Customer, Month, Amount)
VALUES
    ('Tom', 1, 10),
    ('Kate', 1, 60),
    ('Ali', 1, 70),
    ('Tom', 2, 50),
    ('Kate', 2, 40),
    ('Tom', 3, 80),
    ('Ali', 3, 20);


DECLARE @FullTable TABLE(Customer varchar(50), Month int, Amount int);

INSERT INTO @FullTable(Customer, Month, Amount)
SELECT c.Customer, m.Month, ISNULL(mt.Amount, 0)
FROM (SELECT DISTINCT [Month] FROM @myTable) AS m
CROSS JOIN (SELECT DISTINCT Customer FROM @myTable) AS c
LEFT JOIN @myTable AS mt ON m.Month = mt.Month AND c.Customer = mt.Customer


SELECT t1.Customer, t1.Month, t1.Amount, (t1.Amount + ISNULL(t2.sm, 0)) AS AmountSum
FROM @FullTable AS t1
CROSS APPLY (SELECT SUM(Amount) AS sm FROM @FullTable AS t WHERE t.Customer = t1.Customer AND t.Month < t1.Month) AS t2
ORDER BY Month, Customer

Upvotes: 1

Sahi
Sahi

Reputation: 1484

try this: create table #tmp (Customer VARCHAR(10), [month] INT ,Amount INT)

    INSERT INTO #tmp
    SELECT 'Tom',1,10
    union all
    SELECT 'Kate',1,60
    union all
    SELECT 'Ali',1,70
    union all
    SELECT 'Tom',2,50
    union all
    SELECT 'Kate',2,40
    union all
    SELECT 'Tom',3,80
    union all
    SELECT 'Ali',3,20

    ;WITH cte1 AS (
        SELECT [month], ROW_NUMBER() OVER(order by [month] desc) rn 
        FROM (SELECT DISTINCT [month] as [month] FROM #tmp) a
        )
    , cte2 AS (
        SELECT customer, ROW_NUMBER() OVER(order by customer desc) rn 
        FROM (SELECT DISTINCT customer as customer FROM #tmp) b
        )


    SELECT t2.Customer,t2.[month],ISNULL(t1.Amount,0) As Amount 
    into #tmp2
    from #tmp t1
    RIGHT JOIN 
    (select [month],customer from cte1 
    cross apply 
    cte2) t2 ON t1.customer=t2.customer and t1.[month]=t2.[month] 
    order by t2.[month]


    SELECT Customer,[Month] ,SUM (Amount) OVER(partition by customer order by customer ROWS UNBOUNDED PRECEDING ) as Amount 
    FROM #tmp2
    order by [month]

    drop table #tmp
    drop table #tmp2

Upvotes: 1

srp
srp

Reputation: 585

Try this the table name is "a". Using a combination of Cte and sub query. Tried it out in MSSQL2008R2 

 with cte as
(
 select * from (
    select Customer from a 
    group by Customer)c 
 cross join (values (1),(2),(3),(4),(5),(6),(7),(8),(9), (10),(11),(12))a(Months) 
) 
select  Customer,Months,

(select  SUM(total) from 
(select customer , month , sum(amount)as total from a   group by  customer, 
month) as GroupedTable
where GroupedTable.customer= cte.customer and  GroupedTable.month<= cte.Months) as total
  from cte
Group by  Customer,Months
order by Customer,Months

Upvotes: 1

Nolan Shang
Nolan Shang

Reputation: 2328

Do you want get the sum amount every month for each customer , what ever the customer has transaction in that month? In following script, If you have a customer table, you can join the customer table, do not need use (SELECT DISTINCT Customer FROM @myTable)

declare @myTable as TABLE   (Customer varchar(50), Month int, Amount int);
INSERT INTO @myTable(Customer, Month, Amount)
VALUES
    ('Tom', 1, 10),
    ('Kate', 1, 60),
    ('Ali', 1, 70),
    ('Tom', 2, 50),
    ('Kate', 2, 40),
    ('Tom', 3, 80),
    ('Ali', 3, 20),
    ('Jack', 3, 90);

    SELECT c.Customer,sv.number AS Month ,SUM(CASE WHEN t.Month<=sv.number THEN t.Amount ELSE 0 END ) AS Amount
    FROM master.dbo.spt_values AS sv 
    INNER JOIN (SELECT DISTINCT Customer FROM @myTable) AS c ON 1=1
    LEFT JOIN @myTable AS t ON t.Customer=c.Customer
    WHERE sv.type='P' AND sv.number BETWEEN 1 AND MONTH(GETDATE())
    GROUP BY sv.number,c.Customer
    ORDER BY c.Customer,sv.number

----------

Customer                                           Month       Amount
-------------------------------------------------- ----------- -----------
Ali                                                1           70
Ali                                                2           70
Ali                                                3           90
Jack                                               1           0
Jack                                               2           0
Jack                                               3           90
Kate                                               1           60
Kate                                               2           100
Kate                                               3           100
Tom                                                1           10
Tom                                                2           60
Tom                                                3           140

Upvotes: 1

kc2018
kc2018

Reputation: 1460

with cte as
(select *
 from
  (select distinct customer from myTable ) c
  cross join ( values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) t(month))

select cte.customer, cte.month, 
sum(myTable.amount) over (partition by cte.customer order by cte.month) as cumamount
from cte left join myTable
on cte.customer = myTable.customer and cte.month = myTable.month
order by  cte.month, cte.customer desc

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use window function

select Customer, Month,
       sum(Amount) over (partition by customer order by month) Amount
from table t

So, you want some kind of look up tables which has possible months with customers. 

with cte as
(
     select * from (
        select Customer from table 
        group by Customer)c 
     cross join (values (1),(2),(3))a(Months) 
) -- look-up table 

select c.Customer, c.Months, 
       sum(t.Amount) over (partition by c.Customer order by c.Months) Amount 
from cte c left join table t 
      on t.Month = c.Months and t.Customer  = c.Customer

Result :

Customer Months Amount
Tom      1      10
Kate     1      60
Ali      1      70
Tom      2      60
Ali      2      70
Kate     2      100
Ali      3      90
Kate     3      100
Tom      3      140

Upvotes: 3

Ryan Gunn
Ryan Gunn

Reputation: 1169

This should do it for you. Also here is a link to the Microsoft docs regarding aggregation functions.

https://learn.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql

Example:

SELECT 
    Customer, Month, SUM(Amount) as Amount
FROM myTable
    GROUP BY Customer, Month
    ORDER BY Customer, Month

Upvotes: 0

dwir182
dwir182

Reputation: 1549

Try Sum Over Partition By https://learn.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql

This will help you get the idea how to accumulate. If the code i use in postgresql like this Select sum(amount) over(partition by customer, month)

Upvotes: 0

Related Questions