时间戳

首页 > 解决方案 > 按 col 求和和分组时重复行

sql-server - 按 col 求和和分组时重复行

问题描述

如何按部门为每个帐户创建单行，我的意思是总金额？

SELECT  Account
  , AccountName,
    Department,
    Company,
    isNull(VendorCode,'') as VendorCode,
    isNull(UPPER(VendorName),'') as VendorName,
    isNull(ProjectCode,'') as ProjectCode,
    isNull(UPPER(ProjectName),'') as ProjectName,
    FiscalYear,
    FiscalPeriod,
    Sum(Amount)as Amount
FROM [GL]
group by Account, AccountName, Department, Company, VendorCode, VendorName, ProjectCode, ProjectName, FiscalYear, FiscalPeriod
order by AccountName, Department, Company asc
--group by X.Account, x.AccountName, Department, Company, x.VendorCode, x.VendorName, x.ProjectCode, x.ProjectName, x.FiscalYear, x.FiscalPeriod, x.Amount

去

在此处输入图像描述

标签： sql-server

解决方案

根本原因是您选择了冗余属性，例如Company, VendorCode,VendorName等。

结果，您可以看到相同的Account9999 但不同Company

所以如果我在你的鞋子里，我会按我需要的属性分组

SELECT AccountName, Department, SUM(Amount) Amount
FROM [GL]
GROUP BY AccountName, Department
ORDER BY AccountName, Department

推荐阅读