GROUP BY clause error in a SELECT UNION ALL ORDER BY query

Question

I am trying to sort a table after a union all in SQL SERVER

I have read in several places (here for example: How to use order by with union all in sql?) that I need to write :

SELECT  * 
FROM 
        (
            SELECT * FROM TABLE_A 
            UNION ALL 
            SELECT * FROM TABLE_B
        ) dum
-- ORDER BY .....

However, I keep getting an error saying :

Column 'dum.var' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

while I do not have any GROUP BY query ...

can someone explain this to me ?

What is the table structure for `TABLE_A` and `TABLE_B`? Maybe the union itself has a problem. — Tim Biegeleisen, Sep 27 '17 at 11:49
No they have the same structure, i.e. four variables (and dum.var is one of them). In fact, when I omit the 'order by' and run only what I have in the parenthesis, it works well ... — Lili, Sep 27 '17 at 11:50
Is this the exact query you are using? Or do you maybe have some MAX, MIN, SUM, AVG or whatever in your query? — Tyron78, Sep 27 '17 at 11:50
Oh wait ... in fact in the 'order by' I use a sum ... when deleting this sum this works well. Thanks Tyron78 !!! — Lili, Sep 27 '17 at 11:52
that's why you require a group by... could you post the complete query? — Tyron78, Sep 27 '17 at 11:53

score 1 · Answer 1 · answered Sep 27 '17 at 11:57

Thanks to Tyron78 in the comments above, I spotted the mistake.

The actual query was :

SELECT * 
FROM
(
SELECT
        *
FROM
#TABLE1
UNION ALL
SELECT
        *
FROM
#TABLE2
) AS Z
ORDER BY sum([Units]) DESC, [Distance], [ID], [Product]

The problem is in the sum() since I had already summed before in both table1 and table2. So the good query is the following :

SELECT * 
FROM
(
SELECT
        *
FROM
#TABLE1
UNION ALL
SELECT
        *
FROM
#TABLE2
) AS Z
ORDER BY [Units] DESC, [Distance], [ID], [Product]

Thanks all!

score 0 · Answer 2 · answered Sep 27 '17 at 11:58

You will need to provide the column name on which you want to sort the resultset.

SELECT  * 
FROM 
    (
        SELECT * FROM TABLE_A 
        UNION ALL 
        SELECT * FROM TABLE_B
    ) dum
order by <column_name> [asc|desc]

The must be same in both the tables.

score 0 · Accepted Answer · answered Sep 27 '17 at 12:01

DECLARE @t1 TABLE(
  ID INT
  ,Val INT
);

DECLARE @t2 TABLE(
  ID INT
  ,Val INT
);


INSERT INTO @t1 VALUES (1, 10), (2, 20), (3, 30);
INSERT INTO @t2 VALUES (1, 40), (2, 50), (3, 60);

 SELECT *
 FROM (
   SELECT x.ID, SUM(x.Val) SumVal
   FROM (
     SELECT ID,
            Val
       FROM @t1
     UNION ALL
     SELECT ID,
            Val
       FROM @t2
   ) x
   GROUP BY x.ID
) y
ORDER BY SumVal

GROUP BY clause error in a SELECT UNION ALL ORDER BY query

3 Answers3