PostgreSQL and percentages?

vvalter · #1 (**permalink**) 01-05-12, 07:02

Hello

I have a table looking like:

ID | Sum | Tax
----------------
0 | 100 | 20
1 | 1000 | 200
2 | 100 | 45
3 | 1000 | 0

AND i need a query, which selects ALL rows WHERE tax IS NOT 20% of SUM.
So the result should look like this:

ID | Sum | Tax
----------------
2 | 100 | 45
3 | 1000 | 0

But i don't know, how to calculate percentages in postgresql

Can anyone help?

shammat · #2 (**permalink**) 01-05-12, 07:23

Quote:

Originally Posted by vvalter

But i don't know, how to calculate percentages in postgresql

No different than with other systems by using a division: (tax / sum) * 100

Assuming sum and tax are decimal columns:

Code:

SELECT *
FROM the_table_with_no_name
WHERE (tax / sum) * 100 <> 20

If tax and sum are integer values, then you need to make sure the division is using the correct data type:

Code:

SELECT *
FROM the_table_with_no_name
WHERE (tax::decimal / sum:decimal) * 100 <> 20

r937 · #3 (**permalink**) 01-05-12, 08:35

i'm not a postgresql user, but would the following not avoid the integer arithmetic issue...

WHERE 100.0 * tax / sum <> 20

shammat · #4 (**permalink**) 01-05-12, 08:52

Quote:

Originally Posted by r937

i'm not a postgresql user, but would the following not avoid the integer arithmetic issue...

WHERE 100.0 * tax / sum <> 20

Good catch! Yes it would avoid it.
Personally I prefer explicit casts simply to document the fact that one needs to pay attention

vvalter · #5 (**permalink**) 01-05-12, 09:02

thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech