时间戳

首页 > 解决方案 > 为什么这个 SUM() 字段没有按预期工作？

sql - 为什么这个 SUM() 字段没有按预期工作？

问题描述

你能解释一下为什么这个查询没有按预期执行 SUM(LI.Unidades) 吗？我希望将具有相同 LINECOUNT 的所有行加在一起。

首先，我在 select 和 group by 中也有 LI.Unidades（也需要没有 SUM() 的 LI.Unidades 字段），但我删除它只是为了测试，但仍然没有正确添加。

SELECT
CASE AL.CodigoEmpresa WHEN 1 THEN '802' WHEN 100 THEN '801' ELSE 'N/A' END AS 'COMPANYID',
D365.Account as 'CUSTACCOUNT',
SUM(LI.Unidades) as 'GROSSWEIGHT',
AL.NumeroLineas as 'LINECOUNT', 
'KG' as 'SALESUNIT',
LI.ImporteBruto as 'GROSSAMOUNT'

FROM CabeceraAlbaranCliente AL inner join LineasAlbaranCliente LI on (AL.NumeroAlbaran = LI.NumeroAlbaran and AL.FechaAlbaran = Li.FechaAlbaran and AL.CodigoEmpresa = LI.CodigoEmpresa)
inner join AALPLA_D365Customers D365 on (AL.CifEuropeo = D365.Tax_exempt_number and D365.Plant = AL.CodigoEmpresa)

WHERE AL.EjercicioAlbaran = '2020' and AL.NumeroAlbaran = '14424' 

GROUP BY D365.Account, AL.CodigoEmpresa, AL.NumeroAlbaran, AL.NumeroLineas, LI.ImporteBruto

这是我得到的结果：

COMPANYID   CUSTACCOUNT GROSSWEIGHT LINECOUNT   SALESUNIT   GROSSAMOUNT
802         40801       0.0000000000    4       KG           555
802         40801       1.0000000000    4       KG           3000
802         40801       4.0000000000    4       KG           1280
802         40801       16.000000000    4       KG           112
802         40801       100.00000000    13      KG           3123
802         40801       225.00000000    13      KG           11
802         40801       500.00000000    13      KG           100
802         40801       550.00000000    13      KG           30
802         40801       750.00000000    13      KG           12
802         40801       1000.0000000    13      KG           1247
802         40801       1080.0000000    13      KG           1
802         40801       1125.0000000    13      KG           8
802         40801       1200.0000000    13      KG           1765
802         40801       1375.0000000    13      KG           987
802         40801       1475.0000000    13      KG           40
802         40801       2425.0000000    13      KG           30
802        40801        7075.0000000    13      KG           984

我想要的是：

COMPANYID   CUSTACCOUNT GROSSWEIGHT LINECOUNT   SALESUNIT   GROSSAMOUNT
802         40801       21.000000000    4       KG           555
802         40801       21.000000000    4       KG           3000
802         40801       21.000000000    4       KG           1280
802         40801       21.000000000    4       KG           112
802         40801       18880.000000    13      KG           3123
802         40801       18880.000000    13      KG           11
802         40801       18880.000000    13      KG           100
802         40801       18880.000000    13      KG           30
802         40801       18880.000000    13      KG           12
802         40801       18880.000000    13      KG           1247
802         40801       18880.000000    13      KG           1
802         40801       18880.000000    13      KG           8
802         40801       18880.000000    13      KG           1765
802         40801       18880.000000    13      KG           987
802         40801       18880.000000    13      KG           40
802         40801       18880.000000    13      KG           30
802         40801       18880.000000    13      KG           984

标签： sqlsql-serversql-server-2014

解决方案

我认为你想要一个累积总和，而不是聚合：

SELECT (CASE AL.CodigoEmpresa WHEN 1 THEN '802' WHEN 100 THEN '801' ELSE 'N/A' END) AS COMPANYID,
       D365.Account as 'CUSTACCOUNT',
       SUM(LI.Unidades) OVER (ORDER BY LI.Unidades) as GROSSWEIGHT,
       AL.NumeroLineas as LINECOUNT, 
       'KG' as SALESUNIT,
       LI.ImporteBruto as GROSSAMOUNT
FROM CabeceraAlbaranCliente AL inner join
     LineasAlbaranCliente LI 
     ON AL.NumeroAlbaran = LI.NumeroAlbaran and
        AL.FechaAlbaran = Li.FechaAlbaran and
        AL.CodigoEmpresa = LI.CodigoEmpresa inner join
        AALPLA_D365Customers D365
        on AL.CifEuropeo = D365.Tax_exempt_number and
          D365.Plant = AL.CodigoEmpresa
WHERE AL.EjercicioAlbaran = '2020' and AL.NumeroAlbaran = '14424'

推荐阅读