MySQL sum column and display last row in column

Question

I have a table of users,subscription packages and various user subscriptions. I need to fetch a sum of all subscription cost and display the latest/last subscription. The latest subscription is the subscription with the highest subscription_id. How can I write my query? My tables are listed as below.

Users table

user_id name
1       John
2       Jane
3       Matthew

Subscription Packages table

package_id  package_name
1           Basic
2           Advanced
3           Premium

User Subscriptions

subscription_id user_id package_id  subscription_cost       date
        1           1       1               2           2014-04-01
        2           2       1               2           2014-04-01
        3           3       1               2           2014-04-01
        4           1       1               2           2014-05-01
        5           1       2               3.5         2014-06-01
        6           2       2               3.5         2014-06-01
        7           2       2               3.5         2014-07-01
        8           1       3               5           2014-07-01
        9           3       2               5           2014-07-01
        10          2       2               3.5         2014-08-01
        11          1       1               2           2014-08-01

My results should be like so

name    total_costs latest_package
John    14.5        Basic
Jane    12.5        Advanced
Matthew  7          Premium

Answer 1

Because you need to do an aggregation anyway, I would go for the group_concat()/substring_index() trick:

select u.user_id, u.name, sum(subscription_cost) as total_costs,
       substring_index(group_concat(p.package_name order by us.date desc), ',', 1) as latest_package
from usersubscriptions us join
     users u
     on us.user_id = u.user_id join
     packages p
     on us.package_id = p.package_id
group by u.user_id;

This assumes that no package names have commas. It is also subject to default limits on the length of the result of group_concat() -- but this method often works in practice.