Reputation: 1511
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
Upvotes: 2
Views: 457
Reputation: 1271231
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.
Upvotes: 1