# Using same table in a UNION multiple times

## All we need is an easy explanation of the problem, so here it is.

I am given a query that is using the same table with same columns and filters multiple times and the only thing is changing is the Type which is given manually. I am thinking there must be a better way to do that.

Query statement is like below:

``````    Select Column 1, Column 2, Sum(Column 3) AS Test, "First Query" AS "Type"
From Table X
Where Column 1 = "Fly"
Group by Column 1, Column 2

UNION ALL

Select Column 1, Column 2, Sum(Column 3) AS Test, "Second Query" AS "Type"
From Table X
Where Column 1 = "Fly"
Group by Column 1, Column 2

UNION ALL

Select Column 1, Column 2, Sum(Column 3) AS Test, "Third Query" AS "Type"
From Table X
Where Column 1 = "Fly"
Group by Column 1, Column 2
``````

Thanks!

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

Use

``````Select X.Column1, X.Column2, Sum(X.Column3) AS Test, Types."Type"
From Table X
CROSS JOIN ( SELECT "First Query" AS "Type"
UNION ALL
SELECT "Second Query"
UNION ALL
SELECT "Third Query" ) AS Types
Where X.Column1 = "Fly"
Group by X.Column1, X.Column2, Types."Type"
``````

Maybe SELECTs in `Types` needs additional `FROM DUAL` or similar, or DB2 uses some another method for to select scalar value – test.

UPDATE (copied from the comment)

You can shorten the CROSS JOIN part with:

``````CROSS JOIN ( values ('First Query'),('Second Query'),('Third Query')) types (type)
``````

FWIW, Db2 also allows

``````CROSS JOIN ( values 'First Query','Second Query','Third Query') types (type)
``````

but that’s non standard I beleive – Lennart

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂