sql query to calculate percentage

Question

In the table below, i need to find the percentage of males who are in the state 'nyc'.

  select count(*) from table where state = 'nyc' and gender = 'male';

gives me the count of males from nyc. I need the % of males from nyc as 75%.

+-------+-------+--------+
| name  | state | gender |
+-------+-------+--------+
| Jon   | nyc   | male   |
+-------+-------+--------+
| obama | LA    | male   |
+-------+-------+--------+
| kat   | nyc   | male   |
+-------+-------+--------+
| andy  | nyc   | male   |
+-------+-------+--------+
| sri   | nyc   | female |
+-------+-------+--------+

desired output:

state, male_percentage--> nyc, 75 %

as 3 are males and 1 is female from total 4 from nyc.

what have you tried so far? Any queries that you built yourself that might fail/produce undesired output? Probably COUNT might help you here somehow? — Olli, Jul 30 '15 at 09:51
why do this in SQL, why not post process this information in PHP ? — Martin, Jul 30 '15 at 09:53
possible duplicate of [Getting percentage of "Count(\*)" to the number of all items in "GROUP BY"](http://stackoverflow.com/questions/3061655/getting-percentage-of-count-to-the-number-of-all-items-in-group-by) — Lelio Faieta, Jul 30 '15 at 09:54
LA (the city) is not a state - although of course Louisiana is!! NYC is not a state either, unless you're thinking 'empire state of mind') — Strawberry, Jul 30 '15 at 10:04

Viktor Bardakov · Accepted Answer · 2015-07-30T10:15:39.240

2

With one select from table )

 select state ,100 * sum( IF(gender = 'male',1,0) ) / count(*) 
    from table where state = 'nyc' 
    group by state

edited Jul 30 '15 at 10:15

answered Jul 30 '15 at 10:08

Viktor Bardakov

866
6
16

score 1 · Answer 2 · answered Jul 30 '15 at 09:55

1

select count(*)/(select count(*) from table where gender='male')*100
from table where state = 'nyc' and gender = 'male';

answered Jul 30 '15 at 09:55

Madhivanan

13,470
1
24
29

score 0 · Answer 3 · answered Jul 30 '15 at 10:05

0

You can do it with subquery:

(select count(*) from table where gender='male' and state = 'nyc')/
(select count(*) from table where state = 'nyc') *100;

but it can be easyer and more advisable IMHO to do it in PHP

answered Jul 30 '15 at 10:05

Lelio Faieta

6,457
7
40
74

Select data from DB better to do with sql queries, not with PHP – Viktor Bardakov Jul 30 '15 at 10:09
@ViktorBardakov I was not speaking about SELECTING but about doing calculations that is more flexible with PHP compared to SQL. Anyway this is just my opinion as I said in my answer – Lelio Faieta Jul 30 '15 at 10:11

score 0 · Answer 4 · answered Jul 30 '15 at 10:15

0

select (select count(*) from table where state='nyc' and gender='male') * 100.0 / count(*)
from table

is about the simplest way.

works in sqlite3

answered Jul 30 '15 at 10:15

gnuchu

1,496
13
21

sql query to calculate percentage

4 Answers4