CODEWITHSUNDEEP
mysqlsql
Justin Kelley
Justin Kelley

Reputation: 107

Sum of values for all accounts using the most recent date for each

Accounts are worth different values over time. I want to know what is the value of all accounts at the most recent date for which the account has data.

Account      Date     Value
Account 1    1/1/19   12
Account 1    1/3/19   32
Account 1    1/12/19  52
Account 2    4/1/18   123
Account 2    4/22/18  42
Account 3    2/1/19   11
Account 3    7/1/18   64
Account 3    8/12/18  74

For this data set, I want my end result to be 105 because 52+42+11 are the values on the most recent date for each account.

Upvotes: 0

Views: 70

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

I like to use a correlated subquery for this:

select a.*
from accounts a
where a.date = (select max(a2.date)
                from accounts a2
                where a2.account = a.account
               );

In particular, this can take advantage of an index on accounts(account, date).

Upvotes: 0

forpas
forpas

Reputation: 164164

First group by account to get the max date of each account and then join to the table: 

select sum(t.value) totalvalue
from tablename t inner join (
  select account, max(date) date
  from tablename
  group by account
) g on g.account = t.account and g.date = t.date

You can also do it with NOT EXISTS:

select sum(t.value) totalvalue
from tablename t 
where not exists (
  select 1 from tablename
  where account = t.account and date > t.date
)

See the demo.
Result:

> | totalvalue |
> | ---------: |
> |        105 |

Upvotes: 1

y-user
y-user

Reputation: 78

do something like 

select max(date), account
from table
group by account

this should give you the latest date associated to each account. then, you can create a CTE and join back to it by account & date. something like this.

with q as (
  select max(date), account
  from table
  group by account
) select t1.account, t1.date, t1.value  
  from table t1 
  join q on t1.account = q.account and t1.date = q.date

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95062

You want to sum up values for those rows where the date is the maximum date for the account:

select sum(value)
from mytable
where (account, date) in (select account, max(date) from mytable group by account);

As of MySQL 8 you should be able to use a window function to avoid reading the table twice:

select sum(value)
from
(
  select value, date = max(date) over (partition by account) as is_latest_for_account
  from mytable
)
where is_latest_for_account;

Upvotes: 1

Iłya Bursov
Iłya Bursov

Reputation: 24156

there are multiple ways of doing it, my preferable way is to do self-join, like this:

select Account, Date, Value
from accounts as a inner join (
    select Account, max(Date) as maxDate
    from accounts
    group by Account
) as t on a.Account = t.Account and a.Date = t.maxDate

Upvotes: 2

Related Questions