how to make sql query for aggregating data on second unit into one average on minute unit

Question

Before

| collected_at        | currency | price   | 
+---------------------+----------+---------+
| 2017-10-03 03:48:00 | BTC      | 4800000 |
| 2017-10-03 03:48:00 | ETH      | 300000  |
| 2017-10-03 03:48:30 | BTC      | 4850000 |
| 2017-10-03 03:48:30 | ETH      | 310000  |
| 2017-10-03 03:49:00 | BTC      | 4800000 |
| 2017-10-03 03:49:00 | ETH      | 320000  |
| 2017-10-03 03:49:30 | BTC      | 4850000 |
| 2017-10-03 03:49:30 | ETH      | 310000  |
| 2017-10-03 03:50:00 | BTC      | 4800000 |
| 2017-10-03 03:50:00 | ETH      | 300000  |
| 2017-10-03 03:50:30 | BTC      | 4810000 |
| 2017-10-03 03:50:30 | ETH      | 310000  |

After

| collected_at     | currency | price(avg) | 
+------------------+----------+------------+
| 2017-10-03 03:48 | BTC      | 4825000    |
| 2017-10-03 03:48 | ETH      | 305000     |
| 2017-10-03 03:49 | BTC      | 4825000    |
| 2017-10-03 03:49 | ETH      | 315000     |
| 2017-10-03 03:50 | BTC      | 4805000    |
| 2017-10-03 03:50 | ETH      | 305000     |

I want to aggregate prices on same minute unit to one average price for each currency like above by only one sql statement. for example,

| 2017-10-03 03:48:00 | BTC      | 4800000 |
| 2017-10-03 03:48:30 | BTC      | 4850000 |

These two rows above should be aggregate into one average price 4825000 like below.

| 2017-10-03 03:48 | BTC      | 4825000    |

I want to do this work only by one sql statement.

For additional information, collected_at column has timestamp data type. And, I am using mysql database.

depend on your db. For [**example**](https://stackoverflow.com/questions/9783745/truncate-date-to-only-hour-minute) in sql server you can truncate to minute. — Juan Carlos Oropeza, Oct 03 '17 at 15:33

score 0 · Answer 1 · answered Oct 03 '17 at 15:48

0

SELECT CONVERT(DATE_FORMAT(collected_at,'%Y-%m-%d-%H:%i:00'),DATETIME) as dt,
       currency,
       AVG(price)
FROM yourTable
GROUP BY CONVERT(DATE_FORMAT(collected_at,'%Y-%m-%d-%H:%i:00'),DATETIME),
       currency,

answered Oct 03 '17 at 15:48

Juan Carlos Oropeza

47,252
12
78
118

or just dt, if typing it all again is not your thing – Strawberry Oct 03 '17 at 16:04
Thank you juan. I really appreciate you. But, can I ask one more? What if I want to express data on 48mins as 49mins? I mean how to convert date like `2017-10-03 03:48:00` into `2017-10-03 03:49:00`? – bumkeyman Oct 03 '17 at 16:28
That doesnt make sense. Can you explain that logic? But if this solve your question, maybe you should create a new one for that. – Juan Carlos Oropeza Oct 03 '17 at 17:53
Okay. I will do that. Thanks a lot. – bumkeyman Oct 04 '17 at 14:12

how to make sql query for aggregating data on second unit into one average on minute unit

Before

After

1 Answers1