0

I need some help with producing a MySQL query for MySQL version 5.7.29

The data set I have looks like this (sample data):

| id | serial_number | reading_value | reading_date        | register_type |
|----|---------------|---------------|---------------------|---------------|
| 1  | 123           | 7492.91       | 2019-11-20 15:37:55 | import        |
| 2  | 123           | 7783.3        | 2019-11-25 11:15:47 | import        |
| 3  | 123           | 0             | 2019-11-26 13:34:01 | export        |
| 4  | 123           | 4.01          | 2019-11-27 13:52:23 | export        |
| 5  | 456           | 7404.93       | 2019-11-24 13:31:06 | import        |
| 6  | 456           | 7758.23       | 2019-11-26 13:35:02 | import        |
| 7  | 456           | 0             | 2019-11-20 15:37:55 | export        |
| 8  | 456           | 0             | 2019-11-26 13:34:01 | export        |

I would like to get the sum of the reading_value of the latest import and latest export for each serial_number to produce a data set like this:

| serial_number | total_value | latest_reading_date |
|---------------|-------------|---------------------|
| 123           | 7787.31     | 2019-11-27 13:52:23 | <--- id 2 and 4 from above table
| 456           | 7758.23     | 2019-11-26 13:35:02 | <--- id 6 and 8 from above table
  • In this resulting data set, the total_value is the sum of the latest import and latest export reading_value of each serial_number
  • the latest_reading_date is the latest (newest) import or export (whichever is latest) date for the serial_number

I have tried the following query, but it gives me the sum of all the import and export values per serial_number instead of just the sum of the latest import and latest export value of each serial_number.

select serial_number, sum(reading_value) as 'total_value', max(reading_date) as 'latest_reading_date'
from t1
group by serial_number

Any assistance will be greatly appreciated.

Morné Lombard
  • 61
  • 8

3 Answers3

1

There is a tricky approach using GROUP_CONCAT function which supports sorting of aggregated values.

SELECT serial_number, 
    SUBSTRING_INDEX(GROUP_CONCAT(IF(register_type = 'import', total_value, NULL) ORDER BY reading_date DESC, id DESC), ',', 1) `latest_import`,
    SUBSTRING_INDEX(GROUP_CONCAT(IF(register_type = 'export', total_value, NULL) ORDER BY reading_date DESC, id DESC), ',', 1) `latest_export`
FROM t1
GROUP BY serial_number

Explanations:

  • GROUP_CONCAT(IF(register_type = 'import', total_value, NULL) ORDER BY reading_date DESC, id DESC) - will produce a comma-separated list of total_values with import type ordered by date and id
  • SUBSTRING_INDEX() - will pick up the first value from the list
  • your desired value is latest_import + latest_export (+check for nulls if they're possible), I omitted that for brevity
Stalinko
  • 3,319
  • 28
  • 31
  • I wrapped this in another `select` query which adds the *latest_import* and *latest_export* values together and it seems to be doing the trick for me :) – Morné Lombard Mar 11 '20 at 15:27
  • An extension to my question: How could you modify your solution to take the **three** latest *import* and **three** latest *export* values for each serial_number and add them together instead of just the latest *import* and latest *export*? I'm asking because I need to do this as well for a different data set. I want to essentially use `SUBSTRING_INDEX()` to get first, second and third values from list. Not only the first value in the list. I hope what I am asking makes sense – Morné Lombard Mar 11 '20 at 15:46
  • @MornéLombard well it's more complicated. There is no simple way to do that using vanilla MySQL. Option 1: get 3 latest values using `SUBSTRING_INDEX()` and then calculate sum of the comma-separated list using custom functions like (https://stackoverflow.com/a/6315125/2244262). Option 2: using `SUBSTRING_INDEX()` pick up every value into separate column like in my solution. So you'll get 6 columns+serial_number. Option 3: in a sub-query calculate 3rd largest `ID` for every `serial_number` and then in an outer query get a sum of all values where ID >= the ID from the sub-query. – Stalinko Mar 12 '20 at 04:09
0

If I understand correctly, you can use window functions before aggregating:

select serial_number, sum(reading_value) as total_value, 
       max(reading_date) as latest_reading_date
from (select t1.*,
             row_number() over (partition by serial_number, register_type order by reading_date) as seqnum
      from t1
     ) t
group by serial_number;

In pre-8.0 versions of MySQL, you can use a correlated subquery:

select serial_number, sum(reading_value) as total_value, 
       max(reading_date) as latest_reading_date
from t1
where t1.reading_date = (select max(tt1.reading_date)
                         from t1 tt1
                         where tt1.serial_number = t1.serial_number and
                               tt1.register_type = t1.register_type
                        )
group by serial_number;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You can do aggregation with correlated sub-query : 

select t1_1.serial_number, sum(t1_1.reading_value) as total_value, 
       max(t1_1.reading_date) as latest_reading_date
from t1_1
where t1_1.reading_date = (select max(t1_2.reading_date) 
                           from t1 as t1_2
                           where t1_2.serial_number = t1_1.serial_number and 
                                 t1_2.register_type = t1_1.register_type
                          )
group by t1_1.serial_number;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52