CODEWITHSUNDEEP
sqlsql-server
roy
roy

Reputation: 1

SQL - how to retrieve aggregation result under condition

for example - given the table players

id name date      deposit
1  tom  1.1.2014  30
2  roy  2.1.2014  40
3  tom  2.1.2014  80
4  liat 4.1.2014  105
5  tom  6.1.2014  30

retrieve players names,date and the sum of deposit of the players who passed the 100 deposit. (the date should be the day that the player passed 100) in the example the result should be:

tom  2.1.2014 110  
liat 4.1.2014 105

thanks roy

Upvotes: 0

Views: 66

Answers (4)

Mohammed
Mohammed

Reputation: 313

Try this:

select p1.name,sum(p1.deposit) as deposit , (select p2.date from players p2 where p2.id = p1.id) 
from players p1 where p1.deposit >= 100
group by p1.id,p1.name

Upvotes: 0

Uri Goren
Uri Goren

Reputation: 13682

Use this technique to calculate cumulative sum in SQL

SELECT p.name, p.date,p.cumsum as total_deposit FROM
(
select t1.name, t1.date, SUM(t2.deposit) as cumsum
from players t1
inner join players t2 on t1.date >= t2.date and and t1.name = t2.nam
group by t1.name, t1.date
) p
WHERE p.cumsum>100
GROUP BY p.name
HAVING  p.date=MIN(p.date)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If you are using a database that support cumulative sum, you can start with:

select p.*,
       sum(p.deposit) over (partition by p.name order by p.date) as cumedeposit,
       sum(p.deposit) over (partition by p.name) as totdeposit
from players p;

The following gets the information about when a player crosses the 100 deposit mark:

select p.name, p.date, p.totdeposit
from (select p.*,
             sum(p.deposit) over (partition by p.name order by p.date) as cumedeposit,
             sum(p.deposit) over (partition by p.name) as totdeposit
      from players p
     ) p
where cumedeposit >= 100 and cumdeposit - deposit < 100;

If your database does not support cumulative sums and/or window functions, you can do pretty much the same thing with correlated subqueries.

Upvotes: 1

user330315
user330315

Reputation:

You didn't specify your DBMS, but this is ANSI (standard) SQL:

with summed as (
  select name, 
         date, 
         sum(deposit) over (partition by name order by date) as deposit
  from players
)
select s1.*
from summed s1
where s1.total_deposit > 100
  and s1.date = (select min(date)
                 from summed s2
                 where s2.name = s1.name
                   and s2.total_deposit > 100)
order by name;

It seems that (at least with Postgres) this is more efficient (but with such a tiny data set this is really hard to tell):

with summed as (
  select name, 
         date, 
         sum(deposit) over (partition by name order by date) as deposit
  from players
), numbered as (
  select s1.*, 
         row_number() over (partition by name order by date) as rn
  from summed s1
  where s1.deposit >= 100
)
select name, date, deposit
from numbered
where rn = 1
order by name;

SQLFiddle example: http://sqlfiddle.com/#!15/d4590/13

But Uri's solution is probably still more efficient with proper indexing.

Btw: date is a horrible name for a column. For one because it is a reserved word, but more importantly it doesn't document what the column contains. A "played date"? A "due date"? A "deposit date"? A "valid until" date?

Upvotes: 2

Related Questions