Commit | Line | Data |
6eaca423 |
1 | |
2 | -- CASE 1 |
3 | SELECT users.name, SUM(commission) AS total |
4 | FROM commissions |
5 | INNER JOIN users ON ( commissions.recipient_id = users.id ) |
6 | WHERE commissions.entry_date > '2007-01-01' |
7 | GROUP BY commissions.recipient_id |
8 | HAVING total > 500 |
9 | ORDER BY total DESC; |
10 | |
11 | -- CASE 2 |
12 | SELECT users.name, aggregates.total FROM ( |
13 | SELECT recipient_id, SUM(commission) AS total |
14 | FROM commissions |
15 | WHERE commissions.entry_date > '2007-01-01' |
16 | GROUP BY commissions.recipient_id |
17 | HAVING total > 500 |
18 | ) AS aggregates |
19 | INNER JOIN users ON(aggregates.recipient_id = users.id) |
20 | ORDER BY aggregates.total DESC; |
21 | |
22 | -- CASE 3 |
23 | SELECT users.name, aggregates.total FROM ( |
24 | SELECT recipient_id, SUM(commission) AS total |
25 | FROM commissions |
26 | WHERE commissions.entry_date > '2007-01-01' |
27 | GROUP BY commissions.recipient_id |
28 | ) AS aggregates |
29 | INNER JOIN users ON(aggregates.recipient_id = users.id) |
30 | WHERE aggregates.total > 500 |
31 | ORDER BY aggregates.total DESC |