dhS dhS - 5 months ago 12
SQL Question

Query is not working accordingly in postgresql

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.