CODEWITHSUNDEEP
mysql
Justus
Justus

Reputation: 3

mysql calculate data between a certain times period within certain dates

Is it possible to calculate data within specific times of the day for a certain period?

I have a large set of data and want to calculate the "Amount" collected between certain time of the day for a specific period.

For example: Below, I want to sum the amount collected between 00:00 - 14:59 as 'Amount 1' and amount collected between 15:00 - 20:59 as 'Amount 2' from the 1st of July to the 31st of July. 'Total Amount' = 'Amount 1' + 'Amount 2'

sum(if(Date between '2012-07-01 00:00:00' and '2012-07-31 14:59:00',(Amount),0)) as 'Amount 1', sum(if(Date between '2012-07-01 15:00:00' and '2012-07-31 20:59:00',(Amount),0)) as 'Amount 2', sum(Amount) as 'Total Amount'

However, the result for 'Total Amount' does not match 'Amount 1'+'Amount 2'.

Upvotes: 0

Views: 2228

Answers (1)

Mike Brant
Mike Brant

Reputation: 71424

You are not putting any restrictions on your total amount field (i.e. no date or time restrictions, so data outside your date/time ranges is being included. You could either add conditional logic to your sum(Amount) like you did for the other fields or add a WHERE clause like this:

WHERE DATE(Amount) BETWEEN '2012-07-01' and '2012-07-31'
AND TIME(Amount) BETWEEN '00:00:00' and '20:59:00'

This would allow your Total Amount to be calculated correctly and also allow you to remove the date range condition in sore time range summation logic.

Upvotes: 1

Related Questions