Mysql query, select, group & sum by minutes

group-by, mysql, sum

Solution

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;

Problem

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 ``` 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! ``` 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. ```

Original source