查询行总数(最近30天)

Question

我有一张桌子p_payments。该表具有列payment_amount、payment_status和createdtime

p_payments
____________________________
payment_status      varchar(25)
payment_amount      decimal(7,2)
createdtime         datetime

如何创建一个计算过去30天的收入总和的查询WHERE payment_status = completed

我的桌子

payments_status     payment_amount     createdtime
----------------------------------------------------------
Completed               1.00           2013-09-29 21:45:02
Denied                  2.00
Failed                  1.50
... etc

Answer 1

SELECT Sum(payment_amount) 
FROM   p_payments 
WHERE  payments_status = 'completed' 
       AND createdtime >= Date_Sub(Curdate(), INTERVAL 30 day) 

Answer 2

SELECT SUM( p.`payment_amount` ) AS `total_payments`
FROM `payments` p
WHERE DATEDIFF(NOW(), p.createdtime) <= 30
  AND p.payment_status = 'Completed'