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

Question

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%

Answer 1

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)

Answer 2

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;