Compute percents from SUM() in the same SELECT sql query

by

There is more to this question than it may seem.

Simple version

This is much faster and simpler:

SELECT property_name
      ,(count(value_a = value_b OR NULL) * 100) / count(*) AS pct
FROM   my_obj
GROUP  BY 1;

Result:

property_name | pct
--------------+----
 prop_1       | 17
 prop_2       | 43

How?

  • You don’t need a function for this at all.

  • Instead of counting value_b (which you don’t need to begin with) and calculating the total, use count(*) for the total. Faster, simpler.

  • This assumes you don’t have NULL values. I.e. both columns are defined NOT NULL. The information is missing in your question.
    If not, your original query is probably not doing what you think it does. If any of the values is NULL, your version does not count that row at all. You could even provoke a division-by-zero exception this way.
    This version works with NULL, too. count(*) produces the count of all rows, regardless of values.

  • Here’s how the count works:

     TRUE  OR NULL = TRUE
 FALSE OR NULL = NULL

    count() ignores NULL values. Voilá.

  • Operator precedence governs that = binds before OR. You could add parentheses to make it clearer:

    count ((value_a = value_b) OR FALSE)

  • You can do the same with 

    count NULLIF(<expression>, FALSE)

  • The result type of count() is bigint by default.
    A division bigint / bigint, truncates fractional digits.

Include fractional digits

Use 100.0 (with fractional digit) to force the calculation to be numeric and thereby preserve fractional digits.
You may want to use round() with this:

SELECT property_name
      ,round((count(value_a = value_b OR NULL) * 100.0) / count(*), 2) AS pct
FROM   my_obj
GROUP  BY 1;

Result:

property_name | pct
--------------+-------
 prop_1       | 17.23
 prop_2       | 43.09

As an aside:
I use value_a instead of valueA. Don’t use unquoted mixed-case identifiers in PostgreSQL. I have seen too many desperate question coming from this folly. If you wonder what I am talking about, read the chapter Identifiers and Key Words in the manual.

More Related Contents:

  1. Aggregate columns with additional (distinct) filters
  2. PostgreSQL: running count of rows for a query ‘by minute’
  3. must appear in the GROUP BY clause or be used in an aggregate function
  4. Two SQL LEFT JOINS produce incorrect result
  5. Fast way to discover the row count of a table in PostgreSQL
  6. Query with LEFT JOIN not returning rows for count of 0
  7. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  8. Return multiple columns of the same row as JSON array of objects
  9. How to include “zero” / “0” results in COUNT aggregate?
  10. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  11. GROUP BY + CASE statement
  12. PL/pgSQL checking if a row exists
  13. Best way to count rows by arbitrary time intervals
  14. Selecting data into a Postgres array
  15. Postgres analogue to CROSS APPLY in SQL Server
  16. Get the distinct sum of a joined table column
  17. Aggregate a single column in query with many columns
  18. How to find a table having a specific column in postgresql
  19. Postgres function returning table not returning data in columns
  20. Conditional SQL count
  21. Count cumulative total in Postgresql
  22. Check if NULL exists in Postgres array
  23. Run a query with a LIMIT/OFFSET and also get the total number of rows
  24. How to use SQL LIKE condition with multiple values in PostgreSQL?
  25. How to create a new database with the hstore extension already installed?
  26. How to perform a select query in a DO block?
  27. Query for count of distinct values in a rolling date range
  28. Join a count query on generate_series() and retrieve Null values as ‘0’
  29. Truncate timestamp to arbitrary intervals
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment