3

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.

9

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 %
| improve this 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

| improve this answer | |
1
SELECT name,COUNT(*), ROUND(100.0*COUNT(name)/(SELECT 100.0* count(name)  FROM t),3) as percentage  
FROM t GROUP BY name

FIDDLE

| improve this answer | |
0

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
| improve this answer | |
0
SELECT name, COUNT(name) as Count, (COUNT(name) / (SELECT COUNT(name) FROM table_name)) AS Percentage
FROM table_name GROUP BY name;
| improve this answer | |
0

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

| improve this answer | |

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.