mysql - SQL按日期分组

我有两个桌子，如下

------------------    --------------------
| leads          |    | leads_tracking    |
------------------    --------------------
| id             |    | tracking_id       |
| lead_id        |    | lead_id           |
| tix            |    | field_name        |
| order_number   |    | date              |
------------------    ---------------------

我需要获取tix的总和并将其按日期分组（订单号不为空）

我试着写SQL：

SELECT DATE_FORMAT(tracking_leads.date, "%m.%d.%Y") as trackDate, SUM(l.tix) as sumValue
FROM leads as l
INNER JOIN tracking_leads ON l.lead_id=tracking_leads.lead_id
WHERE tracking_leads.field_name='tix'
  AND l.is_active = 1
  AND l.is_archive = 0
  AND l.dont_show_in_list=0
  AND order_number <> ''
  and transaktions_nr IS NOT NULL
GROUP BY DATE_FORMAT(tracking_leads.date, "%m.%d.%Y")

我没有所有需要的小组。

请问我的代码有什么问题？

最佳答案

在您的查询中添加order_number并检查结果。您可能会有所了解。尝试这个：

SELECT order_number,DATE_FORMAT(tracking_leads.date, "%m.%d.%Y") as trackDate, SUM(l.tix) as
sumValue

FROM leads as l
INNER JOIN tracking_leads ON l.lead_id=tracking_leads.lead_id

WHERE tracking_leads.field_name='tix'
  AND l.is_active = 1
  AND l.is_archive = 0
  AND l.dont_show_in_list=0
  AND order_number <> ''
  and transaktions_nr IS NOT NULL

GROUP BY order_number,tracking_leads.date