UNION Statement Same Selected Values - Strange Behavior

Question

I've found a very strange behavior in UNIONed select statements with same values.

tried to find something on the web for whats happening, but didn't know how to phrase the search for this issue, it needs explanation, and it is as follows:

Test 1:

SELECT 
        ID, SUM(AMT) AS AMT
    FROM
    (
        SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION
        SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION
        SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION
        SELECT 1 AS ID, 100 AS AMT FROM DUAL
    )
    GROUP BY ID;

Test 2:

SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION
SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION
SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION
SELECT 1 AS ID, 100 AS AMT FROM DUAL;

All these select statements are giving the same result:

enter image description here

So, supposing I'm writing a dynamic code to be generated and executed at run time in one of my apps, and it happens that the code turned up to be as above! how to make this select statement work ?

Is it UNION ALL you're after? – Allan S. Hansen Aug 21 '14 at 06:47 — Allan S. Hansen, Aug 21 '14 at 06:47
Thanks, makes sense to me now – sameh.q Aug 21 '14 at 07:00 — sameh.q, Aug 21 '14 at 07:00

score 1 · Answer 1 · answered Aug 21 '14 at 06:48

You may try using UNION ALL

SELECT 
        ID, SUM(AMT) AS AMT
    FROM
    (
        SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION ALL
        SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION ALL
        SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION ALL
        SELECT 1 AS ID, 100 AS AMT FROM DUAL
    )
    GROUP BY ID;

score 1 · Accepted Answer · edited Aug 21 '14 at 06:53

1

Please try below one

SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION all

SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION all

SELECT 1 AS ID, 100 AS AMT FROM DUAL UNION all

SELECT 1 AS ID, 100 AS AMT FROM DUAL;

edited Aug 21 '14 at 06:53

Vivek S.

19,945
7
68
85

answered Aug 21 '14 at 06:50

ITHammer

26
3

UNION Statement Same Selected Values - Strange Behavior

2 Answers2