【Question】
I have a users table, a subscription packages table and a user subscriptions table. 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:

user_id name

1 John

2 Jane

3 Matthew

Subscription Packages:

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

 

The expected result:

name total_costs latest_package

John 14.5 Basic

Jane 12.5 Advanced

Matthew 7 Premium

 

【Answer】
Without the support of window functions, it’s almost impossible to achieve complicated order-based calculations in MySQL. In this case, we can handle the computations in SPL (Structured Process Language), which generates intuitive code:

	A
1	$select us.subscription_id   subscription_id,us.subscription_cost subscription_cost,u.user_id   user_id,u.name name,sp.package_name package_name from User_Subscriptions us   left join Users u on us.user_id=u.user_id left join Subscription_Packages sp   on us.package_id=sp.package_id order by us.subscription_id
2	=A1.group(user_id;name,~.sum(subscription_cost):total_costs,~.m(-1).package_name:latest_package   )

A2: “~” represents the current group. m gets a record by its sequence number; -1 means getting the last record.

esProc offers JDBC interface to be conveniently embedded into a Java application. (See How to Call an SPL Script in Java).