Thread: Calculating Percentages

I have 'member' table with a 'nationality' column. I want to get the
percentage breakdown of members by nationality, e.g.

American  29%
Canadian  14%
Mexican   11%
...

Is there an efficient way to do this is a single query?

If I were to do:
SELECT nationality, ((COUNT(*) * 100)/(select count(*) from member)) as
percentage FROM member GROUP BY nationality ORDER BY nationality;

would this repeatedly execute the inner query over and over?

Thanks!
David

David Siegal <dsiegal@brave.cs.uml.edu> writes:
> If I were to do:

> SELECT nationality, ((COUNT(*) * 100)/(select count(*) from member)) as
> percentage FROM member GROUP BY nationality ORDER BY nationality;

> would this repeatedly execute the inner query over and over?

No, it'd do it just once, because the sub-select has no dependency on
the outer select.  (If the sub-select used any variables from the outer
level, then it'd have to be done over at each outer row.)

You can tell the difference between these cases by looking at EXPLAIN
output for the query: do-once sub-selects are labeled InitPlans,
do-every-time sub-selects are labeled SubPlans.
        regards, tom lane

David,

> If I were to do:
> SELECT nationality, ((COUNT(*) * 100)/(select count(*) from member)) as
> percentage FROM member GROUP BY nationality ORDER BY nationality;
>
> would this repeatedly execute the inner query over and over?

Yes, it would
Better is:

SELECT nationality, (COUNT(*)*100/total_members) as percentage
FROM member,(SELECT COUNT(*) as total_members FROM members) tot_mem
GROUP BY nationality
ORDER BY nationality

This method runs the grand total only once.


-- 
Josh Berkus
Aglio Database Solutions
San Francisco

>
> SELECT nationality, (COUNT(*)*100/total_members) as percentage
> FROM member,
>       (SELECT COUNT(*) as total_members FROM members) tot_mem
> GROUP BY nationality
> ORDER BY nationality
>
You'll have to GROUP BY nationality, total_members

Remember COUNT returns bigint, so percentage is the
result of an integer division. To obtain floating point results use
(COUNT(*)*100.0/total_members)

> > If I were to do:
>
> > SELECT nationality, ((COUNT(*) * 100)/(select count(*) from member))
as
> > percentage FROM member GROUP BY nationality ORDER BY nationality;
>
> > would this repeatedly execute the inner query over and over?
>
> No, it'd do it just once, because the sub-select has no dependency on
> the outer select.  (If the sub-select used any variables from the
outer
> level, then it'd have to be done over at each outer row.)
>
Really nice piece of analyzer.

Regards, Christoph

Chris, 

> You'll have to GROUP BY nationality, total_members

Oops.  Problem with posting untested SQL ...

> > > would this repeatedly execute the inner query over and over?
> >
> > No, it'd do it just once, because the sub-select has no dependency on
> > the outer select.  (If the sub-select used any variables from the

That's great, Tom.  Yet another place where we've surpassed MS SQL Server in 
query implementation.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco