Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    8

    Unanswered: PostgreSQL and percentages?

    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by vvalter View Post
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not a postgresql user, but would the following not avoid the integer arithmetic issue...

    WHERE 100.0 * tax / sum <> 20
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by r937 View Post
    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

  5. #5
    Join Date
    Dec 2011
    Posts
    8
    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •