# How can I get the percentage of the table the groups represent?

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%
``````

## 2 Answers

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)
``````
• Excuse me, what means 'tablestat'? – Mareyes Nov 21 '18 at 21:13
• it's just a table alias (can be anything). that's a query that has a result set with one row, "count" that was generated from the `count(*)` on a specific table. – Evan Carroll Nov 21 '18 at 21:31

You are missing the from clause in your outer select

``````SELECT location,
COUNT(*) AS total,
(COUNT(*) * 100)/(SELECT COUNT(*) FROM table1) AS percentage
FROM table1
WHERE extract(year from date) = 2018 GROUP BY location;
``````

It would be better to do it as

``````WITH w_table_count AS (
SELECT COUNT(0) AS table_count
FROM table1
)
SELECT t.location,
t.count(t.date) AS TOTAL
(count(t.date)/wt.table_count) * 100 AS percentage
FROM table1 t, w_table_count
WHERE extract(year from t.date) = 2018 GROUP BY t.location;
``````
• A CTE in PostreSQL is an optimization fence. – Evan Carroll Nov 20 '18 at 19:02