I have a query with a sum
in it like this:
SELECT
Table1.ID, SUM(Table2.[Number1] + Table2.[Number2]) AS SumColumn
FROM Table1 INNER JOIN
Table3 ON Table1.ID = Table3.ID
INNER JOIN
Table2 ON Table3.ID = Table2.ID
WHERE (Table2.[Something] = 'Whatever')
GROUP BY Table1.ID, Table2.[Number1] , Table2.[Number2]
and it gives me a table like this:
ID SumColumn
67 1
67 4
70 2
70 6
70 3
70 6
80 5
97 1
97 3
How can I make it give me a table like this, where the SumColumn is summed, grouped by the ID column?
ID SumColumn
67 5
70 17
80 5
97 4
I cannot GROUP BY
SumColumn because I get an error (Invalid column name 'SumColumn'.) COALESCE
doesn't work either. Thanks in advance.
EDIT:
Just grouping by the ID gives me an error:
[Number1, Number2 and the other column names that I'm selecting] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
EDIT 2
No idea why but just grouping by Table.ID now seems to work. Thanks to all who posted the correct answer, I wish I could tick them all!
SELECT Table1.ID ....GROUP BY Table1.Att1
should give an error that you can't selectID
as it is not in theGROUP BY
or an aggregate function- Are these both the same column and you forgot to change one in your example?SUM(Number1 + Number2)
is OK.Number1, Number2
isn't.Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.