MySQL - 加法，减法和计算天数

Question

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))

Answer 1

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 . . .;