Join the vote table and summarize all the votes

Question

I have two tables. One of them contains quotation marks, and the other contains all voice data (+1 or -1) for each quote. For demonstration purposes, I made simplified versions of two tables:

Quotes

+----+-----------------------------------------------------------------------+ | ID | quote | +----+-----------------------------------------------------------------------+ | 1 | If you stare into the Abyss long enough the Abyss stares back at you. | | 2 | Don't cry because it over. Smile because it happened. | | 3 | Those that fail to learn from history, are doomed to repeat it. | | 4 | Find a job you love and you'll never work a day in your life. | +----+-----------------------------------------------------------------------+ 

Votes

 +----+-------+------+ | ID | quote | vote | +----+-------+------+ | 1 | 1 | -1 | | 2 | 1 | -1 | | 3 | 3 | 1 | | 4 | 3 | -1 | | 5 | 3 | 1 | | 6 | 3 | -1 | | 7 | 4 | 1 | | 8 | 4 | 1 | | 9 | 4 | 1 | +----+-------+------+ 

I would like to list all the quotes on my website and show the corresponding vote count. First, the SQL query must read all the quotes and subsequently join the vote table. However, he must finally list the sum of all votes for each quote. The result of the SQL query will look like this:

 +----+-----------------+------+ | ID | quote | vote | +----+-----------------+------+ | 1 | If you stare... | -2 | | 2 | Don't cry... | NULL | | 3 | Those that... | 0 | | 4 | Find a job... | 3 | +----+-----------------+------+ 

What does the SQL query look like described above?

Answer 1

 SELECT `quotes`.`id` as `ID`, `quote`.`quote` as `quote`, SUM(`votes`.`vote`) AS `vote` FROM `quotes` LEFT JOIN `votes` ON `quotes`.`id` = `votes`.`quote` GROUP BY `quotes`.`id` 

gotta do the trick.

Assuming id columns are primary keys (they are unique to each entry).

Answer 2

 SELECT ID, quote, (SELECT sum(vote) from votes where votes.quote=quotes.ID) FROM quotes 

Answer 3

 SELECT q.id, q.quote, SUM(v.vote ) as summ FROM Quotes q LEFT JOIN Votes v ON q.id=v.quote GROUP BY q.id, q.quote ; 

Answer 4

The following should work. A left join means summary totals are included even if there is no line.

 select ID, quote, total_votes from quotes left join (select quote, sum(vote) as total_votes from quotes group by quote) ) as vote_totals on quotes.ID = vote_totals.quote 

Answer 5

 Select q.id, q.quote, sum(v.vote) from quotes q inner join votes v on q.id= v.quote group by v.quote 

Answer 6

 SELECT Quotes.ID ID, Quotes.QUOTE QUOTE, SUM(Votes.vote) VOTE FROM Quotes LEFT JOIN Votes ON Votes.quote = Quotes.id