CODEWITHSUNDEEP
phpmysqlsql
Norman
Norman

Reputation: 6365

Combining these two SQL statements into one

What would be the best way to combine these two SQL statements into one? I tried using union, but seem to be going nowhere.

select id as id from likes where type=1;

select sum(votes) as votes from likes where parent=id;

Here is the likes table:

id  type  parent  country  votes
 1     1       0       US      0
 2     2       1       US      6 // This +
19     3       1       US      3 // This
 3     3       2       US      3
 7     3       2       US      3
 4    10       3       US      1
 5    10       3       US      1
 6    10       3       US      1
10    10       7       US      1
 9    10       7       US      1
 8    10       7       US      1
20    10      19       US      1
21    10      19       US      1
22    10      19       US      1

This is the result I'm trying to achieve:

id | votes
---------------
1  | 9

Upvotes: 4

Views: 105

Answers (4)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

Try this:

SELECT 
  l1.id,
  SUM(l2.votes) AS Totalvotes
FROM likes AS l1
INNER JOIN likes AS l2 ON l1.id = l2.parent 
WHERE l1.type = 1 
GROUP BY l1.id;

See it in action here:

This will give you:

| ID | TOTALVOTES |
-------------------
|  1 |          9 |

Upvotes: 5

Maxim Kumpan
Maxim Kumpan

Reputation: 2625

This should solve your problem, I believe:

SELECT parent AS id, SUM(votes) AS votes FROM likes WHERE parent IN (SELECT id FROM likes WHERE type = 1) GROUP BY parent;

Upvotes: 1

Roger
Roger

Reputation: 1693

You can try this 

select id,sum(votes) as votes from likes where type=1 and parent=id;

Hope this helps

Upvotes: 0

Harsh Gupta
Harsh Gupta

Reputation: 4538

SELECT SUM(votes) AS votes 
FROM likes 
WHERE parent IN (SELECT id FROM likes WHERE type=1)

HTH

Upvotes: 2

Related Questions