PostgreSQL Division In Query Not Working

So I am having a really off day or something, but for the life of me I can not figure this out. I am trying to divide tier1 by total to get a percentage of the total. I thought like this:

(tier1 / total) as per

But no luck. I have CAST these as int and still nothing. I will either get an error and the query won't execute or I will get a value of 0.

Here is what I am trying to work with:

SELECT count(student_id) as total
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 1) as tier1
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 2) as tier2
,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 3) as tier3
FROM national_assessments.aimsweb
WHERE general_outcome_measure = 'PSF' AND fall_september_tier IS NOT NULL

I had this idea as well but no luck:

SELECT tier1,total, tier1/total as test
FROM (
    SELECT count(student_id) as total
    ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 1) as tier1
    ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 2) as tier2
    ,(SELECT count(fall_september_tier) FROM national_assessments.aimsweb WHERE general_outcome_measure = 'PSF' AND fall_september_tier = 3) as tier3
    FROM national_assessments.aimsweb
    WHERE general_outcome_measure = 'PSF' AND fall_september_tier IS NOT NULL
) as test

Integer division truncates fractional digits. Your expression returns a ratio between 0 and 1, which is always truncated to 0.

To get "percentage", first multiply by 100.

To also get fractional digits, cast to numeric (before you divide) - or multiply by 100.0. The presence of a fractional digit in the numeric literal coerces the result to numeric automatically.

Typically, you would round to two fractional digits or something. Use round() for that (only works for numeric).

Adding some other basic optimizations to the query you get:

SELECT total
     , tier1, round((tier1 * 100.0) / total, 2) AS tier1_pct
     , tier2, round((tier2 * 100.0) / total, 2) AS tier2_pct
     , tier3, round((tier3 * 100.0) / total, 2) AS tier3_pct
FROM  (
   SELECT count(*) AS total
        , count(fall_september_tier = 1 OR NULL) AS tier1
        , count(fall_september_tier = 2 OR NULL) AS tier2
        , count(fall_september_tier = 3 OR NULL) AS tier3
   FROM   national_assessments.aimsweb
   WHERE  general_outcome_measure = 'PSF'
   AND    fall_september_tier IS NOT NULL
   ) sub;

Explanation for the counting technique:

• For absolute performance, is SUM faster or COUNT?

StackExchange Database Administrators Q#75622, answer score: 11