pagila=# select staff_id, sum(amount), sum(amount)/(select sum(amount) from payment) from payment group by staff_id;
staff_id | sum | ?column?
----------+----------+------------------------
2 | 33927.04 | 0.50324527330174759862
1 | 33489.47 | 0.49675472669825240138
(2 rows)
RATIO_TO_REPORT in PostgreSQL
I happened to be looking at the [http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#sthref1748 ratio to report function] available in Oracle, and got a little intrigued by it. Mainly I was wondering how possible it would be to do something like this in PostgreSQL. The gist of it is fairly simple to reproduce in SQL; in this example we’ll generate a breakdown of the sales processed by each employee:
or if you want to get a little fancier:
pagila=# select staff_id, sum(amount), round((sum(amount)/(select sum(amount) from payment)) * 100,2) as percent_of_total from payment group by staff_id;
staff_id | sum | percent_of_total
----------+----------+------------------
2| 33927.04 | 50.32
1| 33489.47 | 49.68
(2 rows)
In theory this could be abstracted out into some type of function… or more likely a function & custom aggregate combo. It seems like it would be a pain though, especially if you wanted to do something more complex, like maybe a break down on the percentage of movies we stock based on rating:
pagila=# select rating, round((sum(1)::numeric/(select count(*) from film))*100,2) from film group by rating;
rating | round
--------+-------
PG | 19.40
G | 17.80
NC-17 | 21.00
R | 19.50
PG-13 | 22.30
(5 rows)
On top of this supporting the Oracle syntax would require even more changes… but it’s probably easier to do it in SQL, but if you really want need the Oracle syntax, maybe [http://www.enterprisedb.com/solutions/compatible.do EnterpriseDB] supports it?