SQL - How to use SUM and MAX in same query?

Question

I am working with a MySQL database version 5.0.41 (and PHP 5.2.6, though that may not be relevant to this question).

I have a table called votes with the following fields: id, item_id, vote_value. Each time a user on the site submits a positive vote for an item, a new row is created with the corresponding item_id and a positive number (i.e. 1). When the vote is negative, a row is created with the corresponding item_id and a negative number (i.e. -1). I'd like to select, with one query (if possible), the item_id that has the most votes. To do this, I first need to sum up all the votes for each individual item_id (to get a number like 38 or -14) and then select the maximum for that number. I am not sure how to write the query for that.

Could you help?

Thanks!

"the item_id that has the most votes" means the item that has the highest rating, right? — Zed
– Zed, Commented Aug 22, 2009 at 20:26

Zed · Accepted Answer · 2009-08-22 20:21:35Z

3

SELECT item_id, SUM(vote_value) AS sum
FROM votes
GROUP BY item_id
ORDER BY sum DESC
LIMIT 1

answered Aug 22, 2009 at 20:21

Zed

57.8k9 gold badges79 silver badges101 bronze badges

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

1 Comment

Yuval Karmi Over a year ago

I think I figured it out. Without the GROUP BY clause, the sql will simply select the highest sum of votes regardless of the item_id (regardless of which item the votes belong to). So if I have 20 votes for one word and 40 for another, instead of the max sum showing up as 40, it will show up as 60 - since it's not grouping the votes by anything. Thanks!

VoteyDisciple · Accepted Answer · 2009-08-22 20:31:20Z

1

You could do something like:

SELECT item_id, SUM(vote_value) AS total, COUNT(id) AS c
FROM votes
GROUP BY item_id
ORDER BY total DESC, c DESC
LIMIT 1

edited Aug 22, 2009 at 20:31

answered Aug 22, 2009 at 20:20

VoteyDisciple

37.9k5 gold badges100 silver badges100 bronze badges

3 Comments

Yuval Karmi Over a year ago

thanks! what is the purpose of selecting the count of id (i.e. COUNT(id))?

Zed Over a year ago

MAX should be SUM, and you should also select the item_id

VoteyDisciple Over a year ago

I misread your original question thinking that "number of votes" was a tiebreaker if the SUM was the same. If the number of votes is irrelevant, the COUNT serves no purpose in this query.

Collectives™ on Stack Overflow

SQL - How to use SUM and MAX in same query?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related