mysql - MySQL如何获取每一列的总数

Question

我有一个 SQL 查询，可以获取不同公司的职位数量、毛重等。

MySQL Workbench 中的结果如下所示:

我想在底部添加另一行来计算每列的总数，即工作列的总数为 267。如何为总数添加额外的一行？

SQL查询:

select
            c.Name,
            COUNT(distinct jn.ID) as Jobs,
            SUM(jn.ActualWeight) as GrossWt,
            SUM(jpc.AdvisedQty) AS Units_In,
            SUM(jpd.AdvisedQty) AS Units_Out
            FROM customer c
        LEFT JOIN job_new jn ON jn.JobOwnerID = c.ID
        LEFT JOIN job_pieces jp ON jp.JobID = jn.ID
        LEFT JOIN job_pieces jpc ON jpc.JobID = jn.ID AND jn.CollectMemberID = jn.JobOwnerID
        LEFT JOIN job_pieces jpd ON jpd.JobID = jn.ID AND jn.DeliverMemberID = jn.JobOwnerID
   WHERE jn.IsActive = 1 GROUP BY jn.JobOwnerID

Answer 1

您正在寻找 GROUP BY 修饰符 WITH ROLLUP :

select c.Name,
       COUNT(distinct jn.ID) as Jobs,
       SUM(jn.ActualWeight) as GrossWt,
       SUM(jpc.AdvisedQty) AS Units_In,
       SUM(jpd.AdvisedQty) AS Units_Out
FROM customer c LEFT JOIN
     job_new jn
     ON jn.JobOwnerID = c.ID LEFT JOIN
     job_pieces jp
     ON jp.JobID = jn.ID LEFT JOIN 
     job_pieces jpc
     ON jpc.JobID = jn.ID AND jn.CollectMemberID = jn.JobOwnerID LEFT JOIN
     job_pieces jpd
     ON jpd.JobID = jn.ID AND jn.DeliverMemberID = jn.JobOwnerID
WHERE jn.IsActive = 1
GROUP BY c.Name WITH ROLLUP;

注意:出于两个原因，我还更改了 GROUP BY 键。首先，它确实应该与 SELECT 中使用的列相匹配。其次，您使用的是 LEFT JOINed 表中的值，因此该值可能为 NULL。这通常是不可取的。