时间戳

首页 > 解决方案 > 加入两个表后，Count Wise 求和是错误的

mysql - 加入两个表后，Count Wise 求和是错误的

问题描述

当前输出：

实体名称 总客户 发票金额 网上支付 现金支付 总付款
达卡 20 20000 20900 0 20900

mq_billing 表在“Dhaka” Entity_Name 下有 2 条发票记录，其中“Invoice Amount”应该是 (1000 + 500 = 1500)，但它显​​示 20000，因为 mq_paymentlist 表在“Dhaka”Entity_Name 下有 20 条付款记录。

我们需要分别对每个表记录求和。任何人都可以帮助..

输出应该是：

实体名称 总客户 发票金额 网上支付 现金支付 总付款
达卡 20 1500 20900 0 20900 
SELECT b.Entity_Name, COUNT(b.Customer_Nbr) as 'Total Customer'
, SUM(b.Invoice_Amount) as 'Invoice Amount'
, CASE WHEN p.Payment_Mode = 'Online Payment' THEN SUM(p.Amount) ELSE 0 END 'Online Payment'
, CASE WHEN p.Payment_Mode = 'Cash' THEN SUM(p.Amount) ELSE 0 END 'Cash Payment'
, SUM(p.Amount) as 'Total Payment'
FROM mq_billing b, mq_paymentlist p
WHERE b.Entity_Name = p.Entity_Name
GROUP BY Entity_Name
ORDER BY Entity_Name;

标签： mysqlsqljoingroup-bycase

解决方案

在每个表中分别聚合，然后加入。
您必须在聚合函数中包含CASE表达式：SUM()

SELECT b.Entity_Name, 
       b.`Total Customer`,
       b.`Invoice Amount`,
       p.`Online Payment`,
       p.`Cash Payment`,
       p.`Total Payment`
FROM (
  SELECT Entity_Name, 
         COUNT(Customer_Nbr) AS `Total Customer`,
         SUM(Invoice_Amount) AS `Invoice Amount`
  FROM mq_billing
  GROUP BY Entity_Name
) b INNER JOIN (
  SELECT Entity_Name, 
       SUM(CASE WHEN Payment_Mode = 'Online Payment' THEN Amount ELSE 0 END) AS `Online Payment`,
       SUM(CASE WHEN Payment_Mode = 'Cash' THEN Amount ELSE 0 END) AS `Cash Payment`,
       SUM(Amount) AS `Total Payment`
  FROM mq_paymentlist
  GROUP BY Entity_Name
) p ON b.Entity_Name = p.Entity_Name
ORDER BY b.Entity_Name;

此外，使用带有ON子句的正确连接并在列别名周围使用反引号，而不是单引号。

推荐阅读