I have 5 fields, I want to group by one of them (ProjectCode) and sum another (ContractAmount), leave everything else the same.
I tried to group by 4 and sum the last one but I went from having 1030 rows to 606 :
How it works is that each project can have more than one trade code, I want to get the sum of all the "ContractAmount"s for one project
When I went into SQL I tried to just delete that GROUP BY fields that I didn't want grouped but it turned them into expressions and I got errors because they did not have an expression entered.
I can figure this out using 2 queries but I am sure that there has to be a way to just use one,
Ideas?
Thanks!
Code:
SELECT FOCOSumTotal.ProjectCode, BidQuestoinCount.CountOfQuestionId AS Questions, FOCOSumTotal.Sum AS [Sum Of Change Amount], FOCOSumTotal.ProjectAwardDate, Sum(dbo_Contract.ContractAmount) AS SumOfContractAmount
FROM (FOCOSumTotal INNER JOIN BidQuestoinCount ON FOCOSumTotal.ProjectCode = BidQuestoinCount.ProjectCode) INNER JOIN dbo_Contract ON BidQuestoinCount.ProjectCode = dbo_Contract.ProjectCode
GROUP BY FOCOSumTotal.ProjectCode, BidQuestoinCount.CountOfQuestionId, FOCOSumTotal.Sum, FOCOSumTotal.ProjectAwardDate
HAVING (((FOCOSumTotal.Sum) Is Not Null And (FOCOSumTotal.Sum)<>0))
ORDER BY BidQuestoinCount.CountOfQuestionId, FOCOSumTotal.Sum;