StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow could I simplify this SQL query in Oracle?
text
Body
copied!<p>After searching and reading a little bit I came up with the following SQL query for my application:</p> <pre><code>SELECT ROUND(AVG(CASE WHEN gender = 'M' THEN rating END), 1) avgAllM, COUNT(CASE WHEN gender = 'M' THEN rating END) countAllM, ROUND(AVG(CASE WHEN gender = 'F' THEN rating END), 1) avgAllF, COUNT(CASE WHEN gender = 'F' THEN rating END) countAllF, ROUND(AVG(CASE WHEN gender = 'M' AND UserAge(birth_date) <= 18 THEN rating END), 1) avgU18M, COUNT(CASE WHEN gender = 'M' AND UserAge(birth_date) <= 18 THEN rating END) countU18M, ROUND(AVG(CASE WHEN gender = 'F' AND UserAge(birth_date) <= 18 THEN rating END), 1) avgU18F, COUNT(CASE WHEN gender = 'F' AND UserAge(birth_date) <= 18 THEN rating END) countU18F FROM movie_ratings mr INNER JOIN accounts a ON mr.aid = a.aid WHERE mid = 5; </code></pre> <p>And I'm wondering how can I simplify this, if possible. The <code>birth_date</code> field is of type <code>DATE</code> and <code>UserAge</code> is a function to calculate the age from that date field.</p> <p>The table structures are as follows:</p> <pre><code>[ACCOUNTS] aid(PK), birth_date, gender [MOVIE_RATINGS] mid(PK), aid(PK,FK), rating </code></pre> <p>I'm looking for two things:</p> <ul> <li>General simplifications to the code above that more experienced users know about that I don't.</li> <li>I'm doing this in PHP and for each record I'll have an associative array with all those variables. I'm looking for a way to group them into a multidimensional array, so the PHP code is easier to read. Of course I don't want to do this in PHP itself, it would be pointless.</li> </ul> <p>For instance, something like this:</p> <pre><code>$info[0]['avgAllM'] $info[0]['countAllM'] $info[1]['avgAllF'] $info[1]['countAllF'] $info[2]['avgU18M'] $info[2]['countU18M'] $info[3]['avgU18F'] $info[3]['countU18F'] </code></pre> <p>Instead of:</p> <pre><code>$info['avgAllM'] $info['countAllM'] $info['avgAllF'] $info['countAllF'] $info['avgU18M'] $info['countU18M'] $info['avgU18F'] $info['countU18F'] </code></pre> <p>I don't even know if this is possible, so I'm really wondering if it is and how it can be done.</p> <p>Why I want all this? Well, the SQL query above is just a fragment of the complete SQL I need to do. I haven't done it yet because before doing all the work, I want to know if there's a more compact SQL query to achieve the same result. Basically I'll add a few more lines like the ones above but with different conditions, specially on the date.</p>

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