我想写一个查询,允许我做以下事情:
"查找在特定日期范围内每天开放和关闭的门票总数"。
这是我目前得到的
SELECT
COUNT(tikcet_id) as totalTickets,
COUNT(CASE WHEN ticket_status = 'Complete'
THEN 1
ELSE NULL END) as closedTickets,
DATE(ticket_date)
FROM tickets
WHERE (DATE(ticket_date) BETWEEN DATE('2014-07-18') AND DATE('2014-07-30'))
GROUP BY ticket_date
这是我得到的结果,它可以很好地计算门票,只是没有按我想要的方式分组。
TotalTickets| ClosedTickets| ticket_date
1| 0| 2014-07-21 10:00:00
1| 0| 2014-07-21 10:21:21
0| 1| 2014-07-21 11:45:12
0| 1| 2014-07-22 09:21:24
0| 1| 2014-07-22 09:43:23
我想让他们按天分组,所以,例如,我应该能够看到这样的东西:
TotalTickets| ClosedTickets| ticket_date
2| 1| 2014-07-21
2| 0| 2014-07-22
GROUP BY DATE(ticket_date)
不是GROUP BY ticket_date
你已经在SELECT
条款中做对了。
顺便说一下,更简短的写法是
COUNT(CASE WHEN ticket_status = 'Complete'
THEN 1
ELSE NULL END) as closedTickets,
SUM(ticket_status = 'Complete') as closedTickets,
布尔表达式求值为true
或false
, 1
或0
。