SQL - how to retrieve aggregation result under condition

Question

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

Which DBMS are you using? Postgres? Oracle? – Jan 10 '15 at 11:51 — , Jan 10 '15 at 11:51
sorry for the late respond: sql server – roy Jan 11 '15 at 20:30 — roy, Jan 11 '15 at 20:30

score 2 · Answer 1 · 2015-01-10T12:41:35.727

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?}

score 1 · Answer 2 · answered Jan 10 '15 at 12:57

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.

That's probably more efficient than my solution. Only a single scan over the table required. — , Jan 11 '15 at 21:33

score 0 · Answer 3 · edited May 23 '17 at 10:26

0

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)

edited May 23 '17 at 10:26

Community

1
1

answered Jan 10 '15 at 11:59

Uri Goren

13,386
6
58
110

it needs to return the amount too – Jayvee Jan 10 '15 at 12:39
WHERE p.cumsum>100 AND p.date=MIN(p.date) will throw an error – Jayvee Jan 10 '15 at 18:46
if you group by p.name then you need to aggregate any other row in the select otherwise it will also throw an error. – Jayvee Jan 10 '15 at 19:43

score 0 · Answer 4 · answered Jan 11 '15 at 23:10

0

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

answered Jan 11 '15 at 23:10

Mohammed

313
1
6

SQL - how to retrieve aggregation result under condition

4 Answers4