CODEWITHSUNDEEP
sqlruby-on-railsruby
opticon
opticon

Reputation: 3584

SQL Group By and Order By with Aggregate Functions

I'm putting together a Rails app, and I've run into a bit of an SQL snag. While it's solvable in Sqlite3, when I move it to my Heroku server running Postgres, it chokes and dies. The situation is as follows:

SPORTS
-----------------------------
|   id    |      title      |
-----------------------------
|   1     |  Baseball       |
-----------------------------

SPORTS_VOTE
------------------------------------
|  id  |   sport_id    |    vote   |
------------------------------------
|  1   |       1       |     1     |
------------------------------------

The vote column could be either 1 or -1. I'd like to get a list of the sports ordered by the sum total of the votes. The query I used in Sqlite3 was something like this:

  SELECT s.title, sum(sv.vote)
    FROM sports s 
   INNER JOIN sports_vote sv ON s.id = sv.sport_id 
 GROUP BY s.id
 ORDER BY sum(sv.vote);

In any respectable DB, this query whines about aggregate functions, etc. What is the best way to do this? Am I better off keeping a 'vote' field in the Sports table, and updating this when votes are cast? The Sports_Vote table also records the ID of the user who submitted the vote, so it's needed.

Secondly, I'd like to ideally combine two similar tables, so that I get a nice list of say, Sports AND Cookies in descending order of votes(Hockey ranked #1, Maple Cookies #2, Football #3, etc). Any advice?

Upvotes: 0

Views: 2780

Answers (1)

Diego
Diego

Reputation: 18349

The rule of thumb to prevent errors when using group by is:

  • All columns not aggregated must be included in the group by clause.

Therefore, your query should be:

 SELECT s.id, s.title, sum(sv.vote)
    FROM sports s 
   INNER JOIN sports_vote sv ON s.id = sv.sport_id 
 GROUP BY s.id, s.title
 ORDER BY sum(sv.vote);

OR

 SELECT s.title, sum(sv.vote)
    FROM sports s 
   INNER JOIN sports_vote sv ON s.id = sv.sport_id 
 GROUP BY s.title
 ORDER BY sum(sv.vote);

To combine with a second table and rank you can just use union (the order by will apply to the combined result):

 SELECT s.title, sum(sv.vote) vote_count
    FROM sports s 
   INNER JOIN sports_vote sv ON s.id = sv.sport_id 
 GROUP BY s.title
 UNION   
 SELECT s.title, sum(sv.vote) vote_count
    FROM cookies s 
   INNER JOIN cookies_vote sv ON s.id = sv.sport_id 
 GROUP BY s.title
 ORDER BY vote_count DESC;

About your question on whether you should keep the vote count in the sports table and update when each vote is cast, it depends. Doing so will give you a better performance when generating reports like the above ones (since the group by and the inner join won't be needed anymore), but it will reduce the performance of casting votes (since now you have to insert AND update a second table). So it depends on your priorities.

Upvotes: 4

Related Questions