Get the sum AND the last value of groups

Question

In MySQL, I am trying to group a table according to a field, but in each of these groups output both the sum and the last value of some fields. For each of the problems I have a solution, but not for both.

It would be easier to explain by an example. Suppose this is the data we have:

CREATE TABLE IF NOT EXISTS `trades` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `trader` int(11) NOT NULL,
  `volume` int(11) NOT NULL,
  `cash` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `trades` (`id`, `trader`, `volume`, `cash`) VALUES
(1, 1, 5, 0),
(2, 1, 7, 5),
(3, 1, 4, 2),
(4, 2, 10, 10);

So our table looks as follows:

+----+--------+--------+------+
| id | trader | volume | cash |
+----+--------+--------+------+
|  1 |      1 |      5 |    0 |
|  2 |      1 |      7 |    5 |
|  3 |      1 |      4 |    2 |
|  4 |      2 |     10 |   10 |
+----+--------+--------+------+

Now, in order to group by trader and get the sum of the volumes, we perform:

mysql> select trader, sum(volume) from trades group by trader;                                                                                                 
+--------+-------------+
| trader | sum(volume) |
+--------+-------------+
|      1 |          16 |
|      2 |          10 |
+--------+-------------+

which is easy. Now, in order to get the "last" cash value in each group, where by "last" here I mean "of the largest id" (but in general, this could use any other field for ordering), I can use the following clever idea (see also here):

mysql> select t1.trader, t1.cash
    -> from trades t1 left join trades t2
    -> on t1.trader = t2.trader and t1.id < t2.id
    -> where t2.id is null;                            
+--------+------+
| trader | cash |
+--------+------+
|      1 |    2 |
|      2 |   10 |
+--------+------+

But how can I combine these solutions, or perhaps find a different solution, in order to get a result which looks more or less as follows:

+--------+--------+------+
| trader | volume | cash |
+--------+--------+------+
|      1 |     16 |    2 |
|      2 |     10 |   10 |
+--------+--------+------+

Important note: I don't want to use non robust solutions, that fail when ONLY_FULL_GROUP_BY is enabled.

Another note: I wish to be able to get the "last" value of more than one field, but doing it with respect to a single ordering should be fine.

score 1 · Accepted Answer · edited Mar 19 '16 at 13:23

There are basically three methods of doing this in MySQL:

Using variables and aggregation.
Using joins and aggregation.
Using the substring_index()/group_concat() trick.

The last of these has certain limitations. It turns all values into strings. And the number of intermediate values as a string is limited by the parameter group_concat_maxlen (which can be changed). However, it is the simplest when you are already doing aggregation:

select trader, sum(volume),
       substring_index(group_concat(cash order by id desc), ',', 1) as last_cash
from trades
group by trader;

The method using join looks like:

select t.trader, sum(t.volume),
       max(case when t.id = tt.maxid then cash end) as last_cash
from trades t left join
     (select trader, max(id) as maxid
      from trades t
      group by trader
     ) tt
     on t.trader = tt.trader
group by t.trader;

score 1 · Answer 2 · answered Mar 19 '16 at 13:21

You can use a subquery to find the sum and max id per trader and then join with the table itself to get the cash value;

SELECT t1.trader, t2.volume, t1.cash
FROM trades t1
JOIN (
  SELECT trader, MAX(id) maxid, SUM(volume) volume FROM trades GROUP BY trader) t2
ON t1.id = t2.maxid

An SQLfiddle to test with.

Get the sum AND the last value of groups

2 Answers2