dhS - 1 year ago 50

SQL Question

`Select sum(num) as num, sum(numbr) as numbr`

from

(

(Select 0 as num)

union all

(Select 1 as num)

) t,

(

(Select 2 as numbr)

union all

(Select 3 as numbr)

) t1

giving result:

`num numbr`

2 10

But the correct result should be

`num numbr`

1 5

Answer

You are doing the cross product of a table containing 0 and 1, and a table containing 2 and 3. Try removing the sums:

```
Select num, numbr as numbr from
(
(Select 0 as num)
union all
(Select 1 as num))t,
((Select 2 as numbr)
union all
(Select 3 as numbr)
)t1
```

This gives you:

```
0;2
0;3
1;2
1;3
```

Which will correctly sum to 2 and 10.

Source (Stackoverflow)