PostgreSQL Query – How to Get the Percentage of the Table Groups Represent

countpostgresqlquery

Given a query like this,

CREATE TABLE foo(grp)
AS VALUES ('a'),('a'),('b'),('b'),('b'),('c'),('c'),('c'),('c'),('e');

I can use GROUP BY to aggregate into a,b,c,e, but how can I return the different groups, and the percentage of the total table they represent?

a | 20%
b | 30%
c | 40%
e | 10%

Best Answer

First percentages are always in a range of [0-1) when stored on a computer. Rendering them in a range of a 0-100 is usually a function of the display (app). From wikipedia,

For example, 45% (read as "forty-five percent") is equal to 45 / 100, 45:100, or 0.45.

If you have for example a simple table. 

CREATE TABLE foo(grp)
AS VALUES ('a'),('a'),('b'),('b'),('b'),('c'),('c'),('c'),('c'),('e');

All you must do is 

SELECT foo.grp, count(*) / tablestat.total::float AS percent_total
FROM foo
CROSS JOIN (SELECT count(*) AS total FROM foo) AS tablestat
GROUP BY tablestat.total, foo.grp
ORDER BY grp;

 grp | percent_total 
-----+---------------
 a   |           0.2
 b   |           0.3
 c   |           0.4
 e   |           0.1
(4 rows)

Now if you want percent to be displayed like a string, you can easily do that too. Because it's mere visual display, I suggest not to do this in a database but we can; a function like this is simple and hides the obscuring details of how you want the number rendered, from the math that calculates it:

CREATE FUNCTION display_percent(x double precision)
RETURNS text
AS $$
  SELECT (x*100)::text || '%'
$$ LANGUAGE sql
IMMUTABLE;

COMMENT ON FUNCTION display_percent
  IS $$Render a number in the range of [0-1) as a text-string (ex., 0.17 to '17%')$$;

And there we have it,

SELECT foo.grp, display_percent(count(*) / tablestat.total::float) AS percent_total
FROM foo
CROSS JOIN (SELECT count(*) AS total FROM foo) AS tablestat
GROUP BY tablestat.total, foo.grp
ORDER BY grp;
 grp | percent_total 
-----+---------------
 a   | 20%
 b   | 30%
 c   | 40%
 e   | 10%
(4 rows)
Related Question