Click here to Skip to main content
14,668,708 members
Rate this:
Please Sign up or sign in to vote.
See more:
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]
                  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
Updated 5-Mar-18 21:35pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100