mysql - 将另一个字段的条件转化为总和

Question

我有 2 个表 ticket 和 article，我有这个查询:

SELECT hour(ticket.stats_iso) hour_num, 
       IF(MINUTE(ticket.stats_iso) < 30, 0, 1) interval_num, 
       min(ticket.stats_iso) interval_begin, 
       max(ticket.stats_iso) interval_end, 
       count(ticket.id) countid, 
       sum(ticket.montantTTC) sum_subtotal, 
       (sum(ticket.montantTTC)/count(ticket.id)) as moy
FROM ticket
WHERE ticket.annule = 0
      AND ticket.stats_iso
          BETWEEN '2012-01-01 00:00:00' AND '2012-01-01 23:59:59'
GROUP BY hour_num, interval_num
ORDER BY hour_num, interval_num

我有ticket数量和montantTTC总和的统计数据。

我想知道我加入后的文章数量:

SELECT hour(ticket.stats_iso) hour_num, 
       IF(MINUTE(ticket.stats_iso) < 30, 0, 1) interval_num, 
       min(ticket.stats_iso) interval_begin, 
       max(ticket.stats_iso) interval_end, 
       count(DISTINCT ticket.id) countid, 
       sum(ticket.montantTTC) sum_subtotal, 
       (sum(ticket.montantTTC)/count(ticket.id)) as moy, count(article.id)
FROM ticket, article
WHERE ticket.annule = 0
      AND ticket.stats_iso
          BETWEEN '2012-01-01 00:00:00' AND '2012-01-01 23:59:59'
      AND ticket.uid = article.uid_ticket
GROUP BY hour_num, interval_num
ORDER BY hour_num, interval_num

它可以工作，但是 sum(ticket.montantTTC) sum_subtotal 变为 false...(事实上，通过连接，它可以对许多重复的 ticket 进行求和，因为 1 ticket 有很多文章

count(ticket.id) countid 也有问题，但我可以写 count(DISTINCT Ticket.id) countid。

是否存在对 sum(ticket.montantTTC) sum_subtotal 设定条件的方法？

示例(当然，不起作用):sum(if Ticket.id IS DISTINCT then Ticket.montantTTC) sum_subtotal

非常感谢。

Answer 1

   SELECT HOUR(t.stats_iso) hour_num, 
          ROUND(MINUTE(t.stats_iso)/60) interval_num, 
          MIN(t.stats_iso) interval_begin, 
          MAX(t.stats_iso) interval_end, 
          COUNT(t.id) countid, 
          SUM(t.montantTTC) sum_subtotal, 
          SUM(t.montantTTC)/count(t.id) moy, 
          SUM(COALESCE(a.article_count,0)) article_count
     FROM ticket t
LEFT JOIN (
       SELECT uid_ticket, COUNT(*) article_count
         FROM article 
     GROUP BY uid_ticket 
          ) a
       ON t.uid = a.uid_ticket
    WHERE t.annule = 0
      AND t.stats_iso BETWEEN '2012-01-01' AND '2012-01-02'
 GROUP BY hour_num, interval_num
 ORDER BY hour_num, interval_num