I want to calculate the average number of sold products. Table:
pieces | date | status
-------------------------------------------
1 | 2015-07-27 12:00:01 | sold
2 | 2015-07-27 16:30:43 | sold
1 | 2015-07-28 13:17:11 | sold
1 | 2015-07-29 10:46:00 | returned
I must add all pieces with "sold" status (4), substract all "returned" (1) and divide (2015-07-29 - 2015-07-27) 3.
Can I make this with one query like:
SELECT SUM(pieces WHERE 'sold' - pieces WHERE 'returned' / date(max - min)) WHERE (date >= date_sub(NOW(), INTERVAL 30 days))
Yes. An easy way is with conditional aggregation:
select (sum(case when status = 'sold' then pieces else 0 end) -
sum(case when status = 'returned' then - pieces else 0 end)
) / (1 + datediff(max(date), min(date))
from . . .;