Need separate columns that calculate percentage

Question

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.

Answer 1

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:

• How will the order of operations in this query affect my results?

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 %

Answer 2

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

Answer 3

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

FIDDLE

Answer 4

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

Answer 5

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

Answer 6

SELECT name, COUNT(name) as Count, (COUNT(name) / (SELECT COUNT(name) FROM table_name)) AS Percentage
FROM table_name GROUP BY name;