如何在SQL中的视图中总和数量并使用组 [英] How to sum amount and use a group by in a view in SQL

问题描述

我认为从一个数据库中的不同表读取数据现在我想使用sum函数来总结我通过下面的项目代码与一个项目代码和组链接的总量是我尝试挑战的查询现在经历的是，当我运行此查询时，我想要拉出我要在组中选择的所有字段，我想要的是将一个字段分组，即项目代码

I have view that read data from different table in one database now I want to use sum function to sum the total amount that I link with one project code and group by the project code below is the query that I tried challenge that am experiencing now is when am running this query want me to pull all the field that I want to select on the group by off which what I want is to group with one field which is the project code "

WCS no

。我很清楚，你可以帮忙吗。



我尝试了什么：

".hopping am clear can you please assist.

What I have tried:

SELECT [Advise no], sum(Amount) as Amounts,PaymentDate AS Date, Description, 
                  [Project Type], [Sub Project Type], [Arms of Services], dbo.VIEW_FacilityBasicInfo.Programme, 
                  dbo.VIEW_FacilityBasicInfo.[Sub-Programme], dbo.VIEW_FacilityBasicInfo.[Facility Number], dbo.VIEW_FacilityBasicInfo.[Facility Name], 
                  dbo.VIEW_FacilityBasicInfo.[DEP Act Item No], dbo.DPW_Payments.[WCS no], dbo.DPW_Payments.[Project Description], dbo.DPW_WCSFacilityID.FacilityID, 
                  dbo.DPW_Payments.PaymentIdentifier, dbo.DPW_Payments.[Bas Fund Description]
FROM     dbo.DPW_WCSFacilityID INNER JOIN
                  dbo.VIEW_FacilityBasicInfo ON dbo.DPW_WCSFacilityID.FacilityID = dbo.VIEW_FacilityBasicInfo.[Facility Number] RIGHT OUTER JOIN
                  dbo.DPW_Payments ON dbo.DPW_WCSFacilityID.WCS = dbo.DPW_Payments.[WCS no] LEFT OUTER JOIN
                  dbo.DPW_WCSDescription ON dbo.DPW_Payments.[WCS no] = dbo.DPW_WCSDescription.WCS
WHERE  [Bas Fund Description] = 'DEF-A&L'
Group by [WCS no

推荐答案

你不能这样做：除非在GROUP BY子句中有一个字段，否则它可以使用iun SELECT除非它是聚合函数的一部分。

见这里： [ ^ ]

我们无法确切地告诉您该做什么，因为您的问题非常清楚，我们无法访问您的数据......

You can't do it like that: unless a field in in the GROUP BY clause, it can;t be used iun the SELECT unless it is part of an aggregate function.
See here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]
We can't tell you exactly what to do, as your question is very unclear, and we have no access to your data...

这篇关于如何在SQL中的视图中总和数量并使用组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！