patternsqlMinor
What is the exact Query To add two rows from different coloums?
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.
Actually the answer should be 4:
Table fb:
Table tw:
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:
Or:
The query in the question has one problem:
This is the result of the
Note that values (all
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 eachfollowvalue
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 4Note 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 4Context
StackExchange Database Administrators Q#129295, answer score: 2
Revisions (0)
No revisions yet.