user1955162 user1955162 - 1 year ago 47
SQL Question

How can I select the sum of a column from a table with multiple entries per metric?

I have a table in MySQL which records periodic metrics for certain data points. This table effectively has a record for every set interval for each metric (defined here by metric_id). I want to know how to select the sum of a group of metrics having the latest timestamp for each one.

mysql> SELECT value, timestamp, metric_id FROM datapoint
WHERE metric_id IN (11, 13) ORDER BY timestamp DESC LIMIT 2;
| value | timestamp | metric_id |
| 28 | 2016-07-26 21:37:04 | 13 |
| 4 | 2016-07-26 21:37:05 | 11 |
2 rows in set (0.00 sec)

What I want is to select the sum (32) from the above table given the metric_id's in the IN statement where for each record, it's the latest one by timestamp.

Answer Source

The subquery in the query below identifies the latest record by timestamp for each metric_id group. This is then used to select the values for each of those most recent records, and the values are then summed in the outer query.

SELECT SUM(t1.value)
FROM datapoint t1
    SELECT metric_id, MAX(timestamp) AS timestamp
    FROM datapoint
    GROUP BY metric_id
) t2
    ON t1.metric_id = t2.metric_id AND
       t1.timestamp = t2.timestamp
WHERE t1.metric_id IN (11, 13)