Query is not working accordingly in postgresql

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

if you use union the name of the column doesn't matter it's the order — ave4496
– ave4496, Commented Jun 9, 2016 at 6:47
The query is working as expected. There is a CROSS JOIN there, so rows from each table as duplicated. What exactly do you want to achieve? — Giorgos Betsos
– Giorgos Betsos, Commented Jun 9, 2016 at 6:50
@Giorgos I am not aware that the Cross Join is happening in it.. Thanks for telling... — dhS
– dhS, Commented Jun 9, 2016 at 6:56

small_duck · Accepted Answer · 2016-06-09 06:50:25Z

2

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.

answered Jun 9, 2016 at 6:50

small_duck

3,10421 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dhS Over a year ago

Thanks @small_duck

sagi · Accepted Answer · 2016-06-09 06:51:46Z

That happens because you are CROSS JOINING , every record connect to every record with out a relation condition, which means that in this case, your join becomes this:

NUM | NUMBR
 0      2
 0      3
 1      2  
 1      3

Which SUM(NUM) = 2 and SUM(NUMBR) = 10 .

When joining, you have to specify the relation condition unless this is what you want.

Note: You are using implicit join syntax(comma separated) , you should avoid that and use the explicit syntax and this will help you make sure you are using a relation condition (by the ON clause):

Select sum(num) as num, sum(numbr) as numbr
from
(
    (Select 0 as num)
    union all
    (Select 1 as num)
) t
INNER JOIN 
(
    (Select 2 as numbr)
    union all
    (Select 3 as numbr)
) t1
ON(t.<Col> = t1.<Col1>)

Nuri Tasdemir · Accepted Answer · 2016-06-09 06:53:02Z

1

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

Gives you the cartessian product of tables.

| Num | Number |
|-----|--------|
| 0   | 2      |
| 0   | 3      |
| 1   | 2      |
| 1   | 3      |

Therefore the sum of these are 2 and 10

answered Jun 9, 2016 at 6:53

Nuri Tasdemir

9,8823 gold badges47 silver badges70 bronze badges

Comments

Kayathiri · Accepted Answer · 2016-06-09 07:00:54Z

1

Its correctly working as you wrote. If you want the result as you expected, try this:

Select sum(distinct num) as num, sum(distinct 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

answered Jun 9, 2016 at 7:00

Kayathiri

7711 gold badge15 silver badges27 bronze badges

Collectives™ on Stack Overflow

Query is not working accordingly in postgresql

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related