按列求和分组

ullevi83

我有一个列 (PL.UNITS),我需要在查询结果的底部进行总计,是否可以对已经求和的 PL.UNITS 求和?

请参阅下面的查询。

SELECT ID.DUEDATE AS [DUE DATE], CD.RENEWALDATE, CD.RENEWALSTATUS, CD.CONTRACTNUMBER, L.LOCNAME, L.LOCADDRESS1, L.LOCADDRESS2, L.LOCADDRESS3, L.LOCADDRESS4, L.POSTCODE, SUM(PL.UNITS) AS UNITS from CLIENTDETAILS CD 

INNER JOIN LOCATIONS L ON CD.CLIENTNUMBER = L.CLIENTNUMBER 
INNER JOIN ITEMDETAILS ID ON L.LOCNUMBER = ID.LOCNUMBER
INNER JOIN PLANT PL ON ID.CODE = PL.CODE

WHERE L.OWNER = 210 and L.STATUSLIVE = 1 and ID.DUEDATE > '01/01/2017' 

GROUP BY ID.DUEDATE, CD.RENEWALDATE, CD.RENEWALSTATUS, CD.CONTRACTNUMBER, L.LOCNAME, L.LOCADDRESS1, L.LOCADDRESS2, L.LOCADDRESS3, L.LOCADDRESS4, L.POSTCODE
蜜獾

最好在前端开发中做这种事情。然而,这里有一个 sql-server 的例子(快速而肮脏,但展示了这个想法):

SELECT  COALESCE(a.id, 'total') AS id
,       SUM(a.thing) AS thing_summed
FROM    (
            SELECT '1' id
            ,   1 thing
            UNION
            SELECT '2'
            ,   2 thing
            UNION
            SELECT '1'
            ,   3 thing
        ) AS a
GROUP BY ROLLUP(a.id) 

结果:

+-------+--------------+
|  id   | thing_summed |
+-------+--------------+
| 1     |            4 |
| 2     |            2 |
| total |            6 |
+-------+--------------+

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章