mysql - 将同一个月的所有总金额合并并求和？

Question

我想合并并求和同一个月内的所有 total_amount、quantity 和 total_order。

 SELECT DATE_FORMAT(tbl_date.date, '%b') AS Month, 
  COUNT(tbl_purchase_received_details.purchase_received_id) AS Total_Order, 
  SUM(IFNULL(tbl_purchase_received_details.received_quantity,0)) AS Quantity, 
  IFNULL((tbl_supplier_medicine.price) * 
  SUM(tbl_purchase_received_details.received_quantity),0) AS Total_Amount, 
  tbl_supplier.supplier_name AS Supplier 
 FROM 
  tbl_date 
 LEFT JOIN 
  tbl_purchase_received ON tbl_purchase_received.date_received = tbl_date.date 
 LEFT JOIN 
  tbl_purchase_received_details ON 
  tbl_purchase_received.purchase_received_id = 
  tbl_purchase_received_details.purchase_received_id 
 LEFT JOIN 
  tbl_supplier_medicine ON tbl_supplier_medicine.supplier_medicine_id = 
  tbl_purchase_received_details.supplier_medicine_id 
 LEFT JOIN tbl_supplier ON 
  tbl_supplier.supplier_id = tbl_supplier_medicine.supplier_id 
 WHERE 
  YEAR(tbl_date.date) = YEAR(NOW()) 
 GROUP BY 
  DATE_FORMAT(tbl_date.date, '%b'), 
  tbl_supplier_medicine.supplier_medicine_id 
 ORDER BY 
  tbl_date.date, tbl_supplier_medicine.supplier_medicine_id

Month  Total_Order  Quantity  Total_Amount  Supplier
Jan    0            0         0             NULL
Feb    0            0         0             NULL
Mar    0            0         0             NULL
Apr    0            0         0             NULL
May    0            0         0             NULL
Jun    0            0         0             NULL
Jul    0            0         0             NULL
Aug    0            0         0             NULL
Aug    2            200       2000          Unilever
Aug    1            100       1200          Unilever
Sep    0            0         0             NULL
Oct    0            0         0             NULL
Nov    0            0         0             NULL
Dec    0            0         0             NULL

我想要这个结果

Month  Total_Order  Quantity  Total_Amount  Supplier
Jan    0            0         0             NULL
Feb    0            0         0             NULL
Mar    0            0         0             NULL
Apr    0            0         0             NULL
May    0            0         0             NULL
Jun    0            0         0             NULL
Jul    0            0         0             NULL
Aug    3            300       32000         Unilever
Sep    0            0         0             NULL
Oct    0            0         0             NULL
Nov    0            0         0             NULL
Dec    0            0         0             NULL

Answer 1

您的GROUP BY与查询不匹配。在您选择的列中，您获取每个月/供应商的总计，但您按 supplier_medicine_id 进行分组。

不确定您想要的GROUP BY是什么。如果一个月有多个供应商怎么办？您想将它们显示为单独的行还是汇总在一起？

根据经验:如果将聚合函数与非聚合列混合，则 GROUP BY 将使用所有非聚合列。在您的情况下，您有非聚合列:

• DATE_FORMAT(tbl_date.date, '%b')
• tbl_supplier.supplier_name

因此，您的分组依据应该是:

按 DATE_FORMAT 分组(tbl_date.date，'%b')，tbl_supplier.supplier_name

另一方面，如果您希望每月只显示一行，则需要做好准备，因为同一个月可能会出现多个供应商。在这种情况下，您可以使用GROUP_CONCAT(tbl_supplier.supplier_name) AS供应商