HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

What is the exact Query To add two rows from different coloums?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
exactrowsthewhatquerydifferenttwocoloumsfromadd

Problem

I am totally new to this community, and I have a query below which is giving the wrong answer when I am adding two rows from different columns.

SELECT tw.name, fb.name, SUM(fb.follow + tw.follow) 
FROM fb, tw 
WHERE fb.name = 20 AND tw.name=20;


Actually the answer should be 4:

+------+------+--------------------------+
| name | name | SUM(fb.follow+tw.follow) |
+------+------+--------------------------+
| 20   | 20   |                        8 |
+------+------+--------------------------+


Table fb:

+----+------+--------+
    | id | name | follow |
    +----+------+--------+
    |  1 | 20   |      1 |
    |  2 | 20   |      1 |
    +----+------+--------+


Table tw:

+----+------+--------+
    | id | name | follow |
    +----+------+--------+
    |  1 | 20   |      1 |
    |  2 | 20   |      1 |
    +----+------+--------+

Solution

Several options such as:

SELECT (SELECT SUM(follow) FROM fb WHERE name = 20)
    + (SELECT SUM(follow) FROM tw WHERE name = 20);


Or:

SELECT SUM(follow) FROM (
    SELECT follow FROM fb WHERE name = 20
    UNION ALL 
    SELECT follow FROM tw WHERE name = 20
) as u;


The query in the question has one problem:

SELECT tw.name,fb.name,SUM(fb.follow+tw.follow) 
FROM fb, tw 
WHERE fb.name = 20 AND tw.name=20;


  • values are counted twice because fb CROSS JOIN tw (i.e. fb, tw) duplicates each follow value



This is the result of the CROSS JOIN query without SUMafter duplication:

id  name    follow  id  name    follow
1   20      1       1   20      3
2   20      2       1   20      3
1   20      1       2   20      4
2   20      2       2   20      4


Note that values (all 1) have been replaced by 1, 2, 3, 4 because it is easier to see the problem.

Code Snippets

SELECT (SELECT SUM(follow) FROM fb WHERE name = 20)
    + (SELECT SUM(follow) FROM tw WHERE name = 20);
SELECT SUM(follow) FROM (
    SELECT follow FROM fb WHERE name = 20
    UNION ALL 
    SELECT follow FROM tw WHERE name = 20
) as u;
SELECT tw.name,fb.name,SUM(fb.follow+tw.follow) 
FROM fb, tw 
WHERE fb.name = 20 AND tw.name=20;
id  name    follow  id  name    follow
1   20      1       1   20      3
2   20      2       1   20      3
1   20      1       2   20      4
2   20      2       2   20      4

Context

StackExchange Database Administrators Q#129295, answer score: 2

Revisions (0)

No revisions yet.