Mysql查询,选择,组&总计以分钟计 [英] Mysql query, select, group & sum by minutes
本文介绍了Mysql查询,选择,组&总计以分钟计的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个数据库表,如下所示:
I have a database table like so:
id | donation_type | donation_amount | time_inserted
1 em1 20 2012-12-07 10:01:00
2 em1 50 2012-12-07 10:01:00
3 em1 100 2012-12-07 10:01:00
4 em1 150 2012-12-07 10:02:00
5 em1 100 2012-12-07 10:02:00
6 em1 30 2012-12-07 10:02:00
7 em1 40 2012-12-07 10:03:00
8 em1 65 2012-12-07 11:16:00
我想要一个查询来计算在每分钟内做了多少捐款。
我也想总结捐款数额那一分钟。
I would like a query to count how many donations where made in each minute.
I would also like to sum the donation amount made in that minute.
所以我想返回的结果(使用上面的表格):
So the result I would like to be returned (using the above table would be):
10:00:01 => 3, amount => 170 # 3 donations in 10:01:00, totaling £170
10:00:02 => 3, amount => 280 # 3 donations in 10:02:00, totaling £280
10:00:03 => 1, amount => 40 # 1 donation in 10:03:00, totaling £40
11:16:00 => 1, amount => 65 # 1 donation in 11:16:00, totaling £40
编辑 - 示例我想通过SQL查询返回
EDIT - Example I would like returned by the SQL query
# Example of what I would like returned..
time | donation_count | donation_sum_in_minute
2012-12-07 10:01:00 3 170
2012-12-07 10:02:00 3 280
2012-12-07 10:03:00 1 40
2012-12-07 11:16:00 1 65 #the next hour!
我从下午10点开始使用以下查询来获取捐款总额。不过,我还希望通过分组给他们上面的结果示例。
I am using the below query to grab the total sum of donations since 10am. But I would like to also group them by minute to give me the result example above.
SELECT SUM(donation_amount)
FROM `DONATION`
WHERE `time_inserted` > '2012-12-07 10:00:00'
AND `donation_type` = 'em1';
Returns: 807,563 # the total sum of donations since 10am today.
推荐答案
SELECT
SUM(donation_amount) AS total_donation_amount,
EXTRACT(YEAR from time_inserted) AS year,
EXTRACT(MONTH from time_inserted) AS month,
EXTRACT(DAY from time_inserted) AS day,
EXTRACT(HOUR from time_inserted) AS hour,
EXTRACT(MINUTE from time_inserted) AS minute
FROM `DONATION`
WHERE `time_inserted` > '2012-12-07 10:00:00'
AND `donation_type` = 'em1'
GROUP BY year, month, day, hour, minute;
这篇关于Mysql查询,选择,组&总计以分钟计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文