# Need separate columns that calculate percentage

I have the following table:

``````NAME
Alex
Bob
Bob
Tim
Alex
Roger
``````

I need to produce the following:

`````` Name    Count Percentage
Alex    2     33%
Bob     2     33%
Roger   1     16.6%
Tim     1     16.6%
``````

I have no idea where to start. I can do the initial query for the count which is simple.

Possible with a single `SELECT`:

``````SELECT name, count(*), to_char((count(*) * 100.0
/ sum(count(*)) OVER ()), 'FM990.00" %"') AS percent
FROM   t
GROUP  BY 1
ORDER  BY 1;
``````

`count(*)` is a separate form of the function and slightly faster than `count(<expression>)`. Assuming all columns to be `NOT NULL`, else you may have to use the latter.

You can run the window aggregate function `sum(count(*)) OVER ()` to get the total in the same `SELECT`. Consider the sequence of event in a `SELECT`:

This uses one sequential scan instead of two and is typically twice as fast as using a subquery for the total count.

`to_char()` prettifies the output:

``````name  count  percent
-----------------------
Alex  2      33.33 %
Bob   2      33.33 %
Roger 1      16.67 %
Tim   1      16.67 %
``````

This is a good opportunity to use Window Functions:

``````create table names
(
id serial,
name text
);

insert into names (name)
values ('Alex'), ('Bob'), ('Bob'), ('Tim'), ('Alex'), ('Roger');

select distinct name, count(name) over (partition by name) as num,
round( (count(name) over (partition by name)::numeric /
count(name) over() ) * 100, 1) as pct
from names
order by num desc, name;
``````

This results in output like the following:

``````NAME    NUM         PCT
Alex    2           33.3
Bob     2           33.3
Roger   1           16.7
Tim     1           16.7
``````

You can tweak the rounding code if you want a slightly different format. You can also concatenate a `%` to the `pct` column value, but you would need to cast it to `text` first and the resulting column would be `text` as opposed to `numeric` (which may be fine for your case).

One big advantage this style of query has is that if your table gets more complex, and has more columns, you can still deal with that data, return it in queries even, and yet still get access to the count and percentage data due to the way Window Function scoping works. If you remove the distinct, you could also get it per-row even for the same name.

SQL Fiddle

``````SELECT name,COUNT(*), ROUND(100.0*COUNT(name)/(SELECT 100.0* count(name)  FROM t),3) as percentage
FROM t GROUP BY name
``````

FIDDLE

I feel I'm possibly cheating a little, but this should get your result:

``````with c as(
select count(*) from "the_table"
), p as(
select name,count(*) from "the_table" group by name
)
select
p.name,
p.count,
((p.count * 100) / c.count) percentage
from p,c
order by name
``````
``````SELECT name, COUNT(name) as Count, (COUNT(name) / (SELECT COUNT(name) FROM table_name)) AS Percentage
FROM table_name GROUP BY name;
``````

One way is to use a subquery:

``````select
name,
count(*) as Count,
to_char(
count(*)/(select cast(count(*) as decimal) from Table1)*100,'99.99'
) ||'%' as Percentage
from Table1
group by name
``````

Sample SQL Fiddle