StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>At a trivial level....</p> <pre><code>SELECT type, genre, year, (SUM(like)-SUM(dislike))/COUNT(*) FROM rates WHERE username='$escaped_username' GROUP BY type, genre, year ORDER BY (SUM(like)-SUM(dislike))/COUNT(*) DESC LIMIT 0,1 </code></pre> <p>But you might you want to allow for cases where there's no great difference between, say particular years....</p> <pre><code>SELECT * FROM ( SELECT type, genre, year, (SUM(like)-SUM(dislike))/COUNT(*) as pref FROM rates WHERE username='$escaped_username' GROUP BY type, genre, year UNION SELECT type, genre, '%', (SUM(like)-SUM(dislike))/COUNT(*) as pref FROM rates WHERE username='$escaped_username' GROUP BY type, genre ) ilv ORDER BY ilv.pref DESC; </code></pre> <p>And because there are 3 different fields - that's 6 subqueries you'd need to write for any combination of type/genre/year.</p> <p>Note that neither of the above would give the same result as....</p> <pre><code>SELECT r.type, r.genre, r.year, artist, song_id, song_name FROM rates r, (SELECT type as preftype FROM rates t WHERE username='$escaped_username' GROUP BY type ORDER BY (SUM(like)-SUM(dislike))/COUNT(*) DESC LIMIT 0,1) as pref_type, (SELECT genre as prefgenre FROM rates g WHERE username='$escaped_username' GROUP BY genre ORDER BY (SUM(like)-SUM(dislike))/COUNT(*) DESC LIMIT 0,1) as pref_genre, (SELECT year as prefyear FROM rates y WHERE username='$escaped_username' GROUP BY year ORDER BY (SUM(like)-SUM(dislike))/COUNT(*) DESC LIMIT 0,1) as pref_year WHERE r.type=preftype AND r.genre=prefgenre AND r.year=prefyear AND username='$escaped_username' ORDER BY likes DESC, dislikes ASC </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload