Re: [GENERAL] Nice to have features: Percentage function

On 04/16/2017 09:37 AM, Melvin Davidson wrote:
>
>
> On Sun, Apr 16, 2017 at 12:23 PM, Adrian Klaver

>
>
> *Or, you could just as easily compute inline in SQL:
>
> SELECT datname,
>        pg_size_pretty(pg_database_size(datname))as size_pretty,
>        pg_database_size(datname) as size,
>        (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
>           FROM pg_database)  AS total,
>       ((pg_database_size(datname) / (SELECT SUM(
> pg_database_size(datname))
>                                        FROM pg_database) ) *
> 100)::numeric(6,3) AS pct
>   FROM pg_database
>   ORDER BY datname;*

Yeah, that is doable but I believe the OP is looking for generic
functions that eliminate the need to write out the math for each query.
A quick and dirty example:

test=# create table percent_test(id int, subtotal numeric, sales_tax
numeric);
CREATE TABLE
test=# insert into percent_test values (1, 128, 8.7), (2, 90, 8.5), (3,
256.35, 8.7), (4, 25.50, 8.5);
INSERT 0 4

test=# select id, subtotal, sales_tax as sales_tax_rate,
percent_of(sales_tax, subtotal)::numeric(7, 2) as tax from percent_test;
  id | subtotal | sales_tax_rate |  tax
----+----------+----------------+-------
   1 |      128 |            8.7 | 11.14
   2 |       90 |            8.5 |  7.65
   3 |   256.35 |            8.7 | 22.30
   4 |    25.50 |            8.5 |  2.17

>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com