1

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%
5
2

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)
2
  • Excuse me, what means 'tablestat'? – Mareyes Nov 21 '18 at 21:13
  • 1
    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
1

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;
1
  • A CTE in PostreSQL is an optimization fence. – Evan Carroll Nov 20 '18 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.