CODEWITHSUNDEEP
sqlpostgresqlinteger-division
kaysuez
kaysuez

Reputation: 97

Calculating rates in PostgreSQL

Trying to do something very simple and calculate the signup_flow rate by mailing.

I have a column called signup_flow - each row has an integer value of either 1 or 0. Trying to calculate the rate of signup_flow per mailing.

I am using the following query. SUM(signup_flow) and COUNT(signup_flow) return the correct values per mailing. When I try to do a simple calculated field (sum/count*100) it returns 0. This is my first time working with Postgres, does it not support calculations?

  SELECT mailing, SUM(signup_flow), COUNT(signup_flow),   
         ((SUM(signup_flow)/COUNT(signup_flow))*100) AS rate
    FROM mail_2017_analytic_file
GROUP BY mailing;

Results:

mailing             sum      count   rate
DEC 17 RRD Mailing  2535    1085476  0
JAN 17 RRD Mailing  8275    3695017  0
MAR 17 RRD Mailing  7230    3595594  0
MAY 17 RRD Mailing  5616    2672981  0
JULY 17 RRD Mailing 7837    3741944  0
AUG 17 RRD Mailing  9272    4604807  0
OCT 17 RRD Mailing  7982    4996146  0

Upvotes: 1

Views: 3564

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657982

Use this expression instead:

    SUM(signup_flow) * 100.0 / COUNT(signup_flow) AS rate

Like @Richard suggested, your problem is integer division, where fractional digits are truncated. The manual:

/ ... division (integer division truncates the result)

This spoils the fun when all you would get is fractional digits.

Multiplying by 100 before the division only "helps" if it brings up the rate to 1 or more. (It's still integer division.) Multiplying by 100.0 does the trick, because a numeric constant with a fractional digit is assumed to be type numeric, while a numeric constant consisting of only digits (and small enough) is assumed to be type integer.

Alternatively, cast at least one involved number to float or numeric explicitly, which forces the non-integer type on the whole calculation.

You may want to round() your result to a given number of fractional digits (works for numeric):

    round(SUM(signup_flow) * 100.0 / COUNT(signup_flow), 2) AS rate

Related (with more explanation and links):

Upvotes: 1

Richard Huxton
Richard Huxton

Reputation: 22943

Those values will be integers, so the calculation will use integer arithmetic. That means the division will generally result in zero which you then multiply by 100.

If you are happy with an integer percentage just multiply by 100 first rather than last.

If you want floating point maths cast the sum first.

Upvotes: 0

Related Questions