How do I get the percentage from a group by?

Question

Problem: I want to know the percentage of heights between 0 and 3, between 3 and 5, etc etc.

I'm able to calculate the absolute values here:

SELECT t.classes, count(*) FROM (select case 
    WHEN height BETWEEN 0 and 3 then 0 
    WHEN height BETWEEN 3 and 5 then 1 
    WHEN height BETWEEN  5 and 7 then 2
    WHEN height BETWEEN 7 and 9 then 3
    WHEN height BETWEEN 9 and 11 then 4
    WHEN height > 11 then 5
    end as classes from lkp0201val) t, group by classes order by classes

and I'm able to calculate the total number of points:

select count(*) from lkp0101val;

... but I don't now how to apply that count value to the first statement.

score 1 · Accepted Answer · answered Aug 14 '14 at 15:18

You can do this using a window function to get the total:

SELECT t.classes, count(*),
       1.0*count(*) / sum(count(*)) over () as proportion
FROM (select (case WHEN height BETWEEN 0 and 3 then 0 
                   WHEN height BETWEEN 3 and 5 then 1 
                   WHEN height BETWEEN  5 and 7 then 2
                   WHEN height BETWEEN 7 and 9 then 3
                   WHEN height BETWEEN 9 and 11 then 4
                   WHEN height > 11 then 5
              end) as classes
      from lkp0201val
     ) t
group by classes
order by classes;

Nice. thank you. Window functions seem real useful. Never would have found that. — canisrufus, Aug 14 '14 at 15:25

Erwin Brandstetter · Answer 2 · 2014-08-14T15:55:27.647

1

You don't need a subquery here:

SELECT CASE WHEN height BETWEEN 0 AND 3  THEN 0 
            WHEN height BETWEEN 3 AND 5  THEN 1 
            WHEN height BETWEEN 5 AND 7  THEN 2
            WHEN height BETWEEN 7 AND 9  THEN 3
            WHEN height BETWEEN 9 AND 11 THEN 4
            WHEN height             > 11 THEN 5
       END AS class
      ,count(*) AS ct
      ,round(count(*) * 100.0 / sum(count(*)) OVER (), 2) AS percentage
FROM   lkp0201val
GROUP  BY 1
ORDER  BY 1;

Returns an actual percentage, rounded to 2 fractional digits.

In older versions (or a certain other RDBMS that doesn't have window functions), you can replace it with a simple subquery for the total count:

...
  ,round(count(*) * 100.0 / (SELECT count(*) FROM lkp0201val), 2) AS percentage
...

edited Aug 14 '14 at 15:55

answered Aug 14 '14 at 15:47

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks for the alternative. I happen to find this one a little harder to read. What is the group by 1, order by 1 thing? Why is the 1 necessary? – canisrufus Aug 14 '14 at 16:05
That's a positional reference, so we do not have to repeat the expression. Example: http://stackoverflow.com/questions/15847173/concatenate-multiple-result-rows-of-one-column-into-one-group-by-another-column/15850510#15850510. Also, I find this considerably easier to read. – Erwin Brandstetter Aug 14 '14 at 16:10
Ah, thanks for the explanation. I'm sure being more familiar with SQL would change what is and isn't easy for me to read :) – canisrufus Aug 15 '14 at 16:34
@canisrufus: Clearly a question of personal preference, too. I was referring to the simpler overall query, btw, not the the positional reference in particular. – Erwin Brandstetter Aug 15 '14 at 19:27

How do I get the percentage from a group by?

2 Answers2