查找特定日期范围内每天的总票数 - Find total number of tickets per day within a specific date range

Find total number of tickets per day within a specific date range

本文关键字：每天范围内日期查找 | 更新日期: 2023-09-27

我想写一个查询，允许我做以下事情:

"查找在特定日期范围内每天开放和关闭的门票总数"。

这是我目前得到的

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。