0

I have a table which stores consumed energy measurements. If I want to know the cost of energy I can select rows for given period, then select first and last row, calculate difference and multiply by price. The problem is if there are two tariffs and they switch from time to time:

kWh  | tariff | time_stamp |
123  | G11    |    t+0     |
130  | G11    |    t+1     |
132  | G11    |    t+2     |
140  | G12    |    t+3     |
143  | G12    |    t+4     |
150  | G11    |    t+5     |
161  | G11    |    t+6     |

I need to somehow group by tariff but I need new group every time the tariff changes. So in the above example there would be three groups, not two. There are only two tariffs possible, G11 and G12. Can I use GROUP BY or something else?

grzegorz
  • 331
  • 3
  • 16

1 Answers1

2

You can use a user-defined variable that increments every time the tariff changes.

SELECT tariff, MIN(time_stamp) AS mintime, MAX(time_stamp) AS maxtime
FROM (
    SELECT t1.*, @counter := IF(tariff = @last_tariff, @counter, @counter+1) AS counter, @last_tariff := tariff
    FROM (SELECT *
          FROM yourTable
          ORDER BY time_stamp) AS t1
    JOIN (SELECT @last_tariff := null, @counter := 0) AS vars) AS grouped
GROUP BY tariff, counter

To get the corresponding rows of the table you can join this with the original table, as explained in SQL Select only rows with Max Value on a Column

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thank you for you answer but I can't get it working. When I execute inner SELECT with JOIN, then the counter increases for every row. I suppose the last_tariff variable should be updated somehow. At this moment I think it's always null and thus different than tariff. – grzegorz Jun 06 '16 at 17:27
  • You're right, I forgot to update `@last_tariff`. I've added it. – Barmar Jun 06 '16 at 17:29