CODEWITHSUNDEEP
mysqlsql
Nicolai Iversen
Nicolai Iversen

Reputation: 41

Cumulative sum counting at different days

Halo everyone. I have a table with customer behaviour for different customers at different dates. I need to make a monthly report of these customers, but and one of the columns should include the number of customers with an account different from zero.

My table looks something like this: 

CustomerID      Timestamp   Amount  
5               1           100     
5               1           150
10              1           20
15              1           200
15              1          -150
5               2          -250     
10              2           50
10              2           50

The output from the code should be

Timestamp      #Customers with sum(Amount)<>0
1              3
2              2

Since the account for all 3 are positive in first period but zero for customer 5 in second period.

Until now I have only managed to timestamp the data.

My question is then how can one cumulative sum the accounts for each customer in any timeperiod and count the number of cases where these account are different from zero?

Upvotes: 0

Views: 258

Answers (3)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can get the running sum with a correlated sub-query and get the count of distinct customers for each timestamp with sum <> 0 thereafter.

SELECT timestamp,
    COUNT(DISTINCT customerid) AS count_customer
FROM(
    SELECT customerid, 
        timestamp,
        (
            SELECT SUM(amount) 
            FROM tablename t2
            WHERE t2.customerid = t1.customerid 
            AND t2.timestamp <= t1.timestamp
        ) AS cum_total
 FROM tablename t1
) t 
WHERE cum_total <> 0
GROUP BY timestamp;

Edit: You can try using a variable and see if it performs better.

select timestamp,count(*) 
from (select customerid,timestamp
      ,case when @customer=customerid then @cumsum:=@cumsum+amount else @cumsum:=amount end as rsum
      ,@customer:=customerid
     from (select customerid,timestamp,sum(amount) as amount 
           from tablename 
           group by customerid,timestamp) t
     join (select @cumsum:=0,@customer:='') c
     order by customerid,timestamp
    ) t
where rsum<>0
group by timestamp

Upvotes: 2

Kickstart
Kickstart

Reputation: 21513

Another option.

Uses an inner sub query to get the unique time stamps (if you had a table of these it would probably be far more efficient, as it could use indexes effectively). This is joined to the table to get all the rows for that day or before, and SUMs the amount, using HAVING to exclude those where the sum is negative.

Then the outer query counts the number of customers returned for each timestamp in the inner query.

SELECT sub1.Timestamp, 
        COUNT(sub1.CustomerID) 
FROM 
( 
    SELECT sub0.Timestamp, a.CustomerID, SUM(a.Amount) AS amount_sum 
    FROM 
    ( 
        SELECT DISTINCT Timestamp 
        FROM amount_table 
    ) sub0 
    LEFT OUTER JOIN amount_table a ON sub0.Timestamp >= a.Timestamp 
    GROUP BY Timestamp, 
            CustomerID 
    HAVING amount_sum > 0 
) sub1 
GROUP BY Timestamp

Where this might struggle is returning a row with a count of 0 for a timestamp where there are no customers with a positive amount up to that date. Might not be an issue for you, but if it is:-

SELECT sub1.Timestamp, 
        SUM(IF(amount_sum > 0, 1, 0)) 
FROM 
( 
    SELECT sub0.Timestamp, a.CustomerID, SUM(a.Amount) AS amount_sum 
    FROM 
    ( 
        SELECT DISTINCT Timestamp 
        FROM amount_table 
    ) sub0 
    LEFT OUTER JOIN amount_table a ON sub0.Timestamp >= a.Timestamp 
    GROUP BY Timestamp, 
            CustomerID 
) sub1 
GROUP BY Timestamp

Upvotes: 1

kennasoft
kennasoft

Reputation: 1593

You should consider using a group by clause 

select Timestamp, count(customerID) as CustomersWithNonZeroAmount 
from tableName 
where Amount<>0 group by timestamp

Let me know if this works.

Upvotes: 0

Related Questions