1

My title is terrible but I'm having a hard time wrapping my head around this. I have two tables. Links and Votes. Links contain info about links submitted and the usual jazz, votes contains a foreign key link_id and a karma_up and karma_down value (both unsigned ints). Because I want to limit votes to one per user and record vote time I opted for the two tables method. What I want to do though is display the sum'ed karma of a link. I need to be able to grab all the links and then their one to many votes which are sum'ed up SUM(karma_up - karma_down). All I have been able to do is SUM up the entire column which doesn't do me any good.

Hopefully someone just "gets" what I'm trying to do but till then I will be updating this post with as much relevant information as possible.

UPDATE For anyone interested, my final query is

SELECT links.*, (SUM(karma_up) - SUM(karma_down)) AS karma
FROM links, votes
WHERE links.id = votes.link_id
GROUP BY votes.link_id
Improve this question
4
  • Can you post your current select statement? Commented Nov 21, 2009 at 23:45
  • Son of a bitch you two are quick! Awesome work, Kudos all around! Commented Nov 21, 2009 at 23:50
  • The idea (in one of the answers) is true, links will not be returned for those that have no votes. To get those you will need to use a left outer join. Commented Nov 21, 2009 at 23:59
  • Yea, I only went with the first answer because I insert a default vote for every new link with the assumption that a user would vote for his own submission. The unintended consequence of that was the simplification of this query :) Commented Nov 22, 2009 at 0:01

4 Answers 4

Reset to default
5

You could post your schema to be sure, but something like this should work

select v.link_id, (sum(karma_up) - sum(karma_down)) as points from 
Links l, Votes v
where l.link_id = v.link_id group by v.link_id

That should give you the points per link_id.

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

1 Comment

Beat me to it :) You may want to add an outer join, if links with no votes are needed.
2

You need to do something like this:

SELECT Links.LinkId, SUM(Votes.karma_up) - SUM(Votes.karma_down) AS karma FROM Links
    LEFT JOIN Votes ON Links.LinkId = Votes.LinkId
    GROUP BY Links.LinkId

Note the use of LEFT JOIN to ensure that links without votes are also included.

Improve this answer

1 Comment

I like this too, I don't quite need this because when I insert a link I also insert a default +1 karma vote so every link has at least one starting vote row. A point regardless!
1

Just one more thing I'd like to mention. Unless a vote is allowed to have both up and down karma at the same time (unlikely from the desription), you would better off replacing them karma_up and karma_down with a single karma_delta column. Then set it to -1 or 1 depending on whether it's down or up respectively.

This would simplify your query even more:

SELECT links.*, SUM(karma_delta) AS karma
FROM links, votes
WHERE links.id = votes.link_id
GROUP BY votes.link_id
Improve this answer

1 Comment

That's a good idea. I got the karma_up and karma_down idea for when they were attached to the link table itself but I wanted to see how many up/down votes there were and implement a duplicate check so I'll make those changes!
0

Points per link id, and zero karma if there are no votes:

select l.*, coalesce(sub_karma, 0) as karma
from links as l
    left join (
        select link_id, sum(karma_up - karma_down) as sub_karma
        from votes
        group by link_id
    ) as v using (link_id)
;
Improve this answer

2 Comments

Same comment as above although I understand yours slightly less, a point regardless!
The left join () as v is a sub-query to do the summing separately before joining to the links table. paxdiablo's suggestion to use karma_delta is a better approach. Simpler query to write and easier for the query optimizer (MySQL) to execute efficiently.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.