Mysql select max maximum sum values

Question

I have a database which contains some picture data and a linking table. The tables are build-up like this:

---pictures---
picid    Lat  Lon
1        5    6
2        7    31
3        31   43
4        -3   35

---user2pictures---
picid   userid  vote
1       1      1
1       2      1
3       1     -1
3       2      1
4       2     -1

The table pictures contains a picture id and some data about the image, the table user2votes contains vote data from the images. Each user is able to vote on images, but they can only vote 1 time, so the vote will be either 1 (like) or -1 (dislike).

I want to select everything from the pictures table from pictures which have the highest number of votes. Pseudoquery which might explain better what I want: SELECT * FROM pictures WHERE (SELECT MAX(SUM(vote)) FROM user2pictures LIMIT 12

In this example picture 1 would return at the top, picture 3 would follow and picture 4 as the last one. I really don't know how to solve this, some help in the right direction would be very much appreciated!

Thanks!

Yogendra Singh · Accepted Answer · 2013-05-29 20:28:31Z

4

The answer is to JOIN the tables, SUM the votes, and ORDER high-to-low by the sum of the votes

SELECT pictures.*, SUM(user2pictures.vote) AS VoteTotal
FROM pictures
JOIN user2pictures ON pictures.picid = user2pictures.picid
GROUP BY pictures.picid
ORDER BY VoteTotal DESC
LIMIT 12

edited May 29, 2013 at 20:28

Yogendra Singh

34.5k7 gold badges66 silver badges73 bronze badges

answered May 29, 2013 at 20:27

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

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

Comments

echo_Me · Accepted Answer · 2013-05-29 20:31:07Z

4

try this

   select p.`picid`, `Lat`, `Lon` from pictures p
   inner join user2pictures u2p
   on p.picid = u2p.picid
   group by u2p.picid
   order by sum(vote) desc
   limit 12

DEMO

answered May 29, 2013 at 20:31

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

1 Comment

Jef Over a year ago

Thanks for your input! Also thanks for the demo, I didn't know such thing existed, very useful for testing.

Barranka · Accepted Answer · 2013-05-29 20:33:35Z

2

I'll assume that you also want to show the pictures with no votes. So, you can try this:

select 
    p.picId, sum(v.vote) as votes
from 
    pictures as p
    left join user2pictures as v on p.picId = v.picId
group by
    p.picId
order by
    sum(v.vote) desc
limit 12;

The left join is what lets you show the pictures with no votes (the column votes will have the value 0)

Hope this helps

answered May 29, 2013 at 20:33

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Collectives™ on Stack Overflow

Mysql select max maximum sum values

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related