多连接中的行数总和

yfwxisqw  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(345)

我正在尝试对多个联接进行行求和。我只能为单个表找到它,我使用了以下查询。

SELECT sd.Descrip
    ,0 AS TotalLedgerNumber
    ,ss.Total
FROM tblServiceSummary ss
LEFT JOIN servdesc sd ON ss.Service_time = sd.ServDescID
JOIN funct fn ON fn.FunctID = ss.FunctionID
GROUP BY sd.Descrip

UNION ALL

SELECT 'Total'
    ,SUM(IFNULL(NULL, 0)) AS TotalLedgerNumber
    ,sum(ss.Total)
FROM tblServiceSummary ss
LEFT JOIN servdesc sd ON ss.Service_time = sd.ServDescID
JOIN funct fn ON fn.FunctID = ss.FunctionID
WHERE fn.StartDate BETWEEN "2018-9-18"
        AND "2018-9-18"
GROUP BY sd.Descrip

这给了我这样的结果

我想显示上面显示的所有行和所有行的总和
我试着在上面和下面的查询中添加remove group by和where。如果我试着从下面删除group by..它显示为total | null | null,但这不起作用。请建议

qvk1mo1f

qvk1mo1f1#

如果您尝试对组后面的所有行求和,则可以封装查询并在其后面求和。
例子:

SELECT
   SUM(Descrip)
FROM
   (

SELECT sd.Descrip
    ,0 AS TotalLedgerNumber
    ,ss.Total
FROM tblServiceSummary ss
LEFT JOIN servdesc sd ON ss.Service_time = sd.ServDescID
JOIN funct fn ON fn.FunctID = ss.FunctionID
GROUP BY sd.Descrip

UNION ALL

SELECT 'Total'
    ,SUM(IFNULL(NULL, 0)) AS TotalLedgerNumber
    ,sum(ss.Total)
FROM tblServiceSummary ss
LEFT JOIN servdesc sd ON ss.Service_time = sd.ServDescID
JOIN funct fn ON fn.FunctID = ss.FunctionID
WHERE fn.StartDate BETWEEN "2018-9-18"
        AND "2018-9-18"
GROUP BY sd.Descrip

) summedRows

相关问题