查询MySQL以显示条目数和排序

Question

我有一个包含以下列的MySQL数据库：

bookingdatetime (DateTime)
promo (Varchar)

我想查询数据库以计算使用的不同促销代码的数量(如果促销行不为空)，并给出如下输出：

promo               amount appeared that month    
example - 01/20     13

<promocode - mm/yy> <number of times that code was used in that month>

这就是我到目前为止所知道的：

SELECT DATE_FORMAT(bookingdatetime, '%m/%y') `promo`, 
       SUM(promo != '') 'count'
FROM `reservations`
WHERE pickuploc = 'la'
GROUP BY DATE_FORMAT(bookingdatetime, '%m/%y'), YEAR(bookingdatetime)
ORDER BY YEAR(bookingdatetime), DATE_FORMAT(bookingdatetime, '%m/%y')

我需要在%m%y之前获得促销中使用的促销代码，并且我需要不显示计数是否有0个条目。

Answer 1

这应该能起到作用：

SELECT
  CONCAT(promo, ' - ', DATE_FORMAT(bookingdatetime, '%m/%y')) AS promo,
  COUNT(*) AS count
FROM reservations
WHERE pickuploc = 'la'
  AND COALESCE(promo, '') <> ''
GROUP BY
  DATE_FORMAT(bookingdatetime, '%m/%y'),
  promo
HAVING COUNT(*) > 0
ORDER BY
  DATE_FORMAT(bookingdatetime, '%y'),
  DATE_FORMAT(bookingdatetime, '%m')

示例输出：

promo         count
foo - 01/19   2
bar - 01/19   1
foo - 02/19   2
bar - 03/19   1
foo - 03/19   1

SQL Fiddle示例：http://sqlfiddle.com/#!9/86083/5