The UNION aspect is irrelevant here. DSatino explains what UNION does quite well, but it's not related to this problem.
The problem is that whenever GROUP BY is used in a query the SELECT clause can only be made up of :
- Either items in the GROUP BY clause
- or items that are aggregated (Sum(), Count(), etc)
It is logically nonsense to ask for items that are not grouped and not aggregated as there is no possible way that it could determine which value you were after.
Consider the following data (in a table [Table1]) :
- Field1 Field2
-
AAA 1
-
AAA 2
-
AAA 3
-
BBB 7
-
BBB 9
If the SQL said :
- SELECT [Field1]
-
, [Field2]
-
FROM [Table1]
-
GROUP BY [Field1]
How could the server grant the request? What value to return for [Field2] across the three records where
[Field1] = "AAA"
?
The SELECT clause would need to be something like :
- SELECT [Field1]
-
, Sum([Field2]) As SumofField2