mysql> select concat(city,",",state) as CITYSTATE, sum(case when gender between 1 and 2 then 1 else 0 end) as PAID_DELETES, sum(case when gender=1 then 1 else 0 end) as FEMALE, sum(case when gender=2 then 1 else 0 end) as MALE, sum(case when gender=1 then 1 else 0 end)/sum(case when gender between 1 and 2 then 1 else 0 end) as RATIO from us_am_am_member where first_name='' group by CITYSTATE order by FEMALE desc limit 100; +---------------------+--------------+--------+------+--------+ | CITYSTATE | PAID_DELETES | FEMALE | MALE | RATIO | +---------------------+--------------+--------+------+--------+ | New York,33 | 2498 | 231 | 2267 | 0.0925 | | Houston,45 | 1591 | 113 | 1478 | 0.0710 | | Chicago,14 | 1450 | 108 | 1342 | 0.0745 | | Dallas,45 | 797 | 92 | 705 | 0.1154 | | Los Angeles,5 | 1062 | 91 | 971 | 0.0857 | | Atlanta,11 | 886 | 88 | 798 | 0.0993 | | Austin,45 | 815 | 67 | 748 | 0.0822 | | Washington,9 | 748 | 62 | 686 | 0.0829 | | san antonio,45 | 686 | 60 | 626 | 0.0875 | | Denver,6 | 749 | 58 | 691 | 0.0774 | | Philadelphia,39 | 620 | 57 | 563 | 0.0919 | | Boston,22 | 648 | 54 | 594 | 0.0833 | | San Francisco,5 | 643 | 51 | 592 | 0.0793 | | san diego,5 | 788 | 50 | 738 | 0.0635 | | Brooklyn,33 | 653 | 50 | 603 | 0.0766 | | Miami,10 | 564 | 45 | 519 | 0.0798 | | Seattle,50 | 687 | 43 | 644 | 0.0626 | | Portland,38 | 470 | 41 | 429 | 0.0872 | | Minneapolis,24 | 708 | 40 | 668 | 0.0565 | | Las Vegas,29 | 439 | 40 | 399 | 0.0911 | | Salt Lake City,46 | 422 | 39 | 383 | 0.0924 | | Charlotte,34 | 681 | 37 | 644 | 0.0543 | | San Jose,5 | 368 | 36 | 332 | 0.0978 | | Phoenix,3 | 495 | 35 | 460 | 0.0707 | | Indianapolis,15 | 427 | 35 | 392 | 0.0820 | | Orlando,10 | 364 | 34 | 330 | 0.0934 | | Fort Lauderdale,10 | 391 | 34 | 357 | 0.0870 | | Birmingham,1 | 308 | 32 | 276 | 0.1039 | | Tampa,10 | 423 | 31 | 392 | 0.0733 | | plano,45 | 246 | 29 | 217 | 0.1179 | | Raleigh,34 | 372 | 29 | 343 | 0.0780 | | Arlington,49 | 362 | 27 | 335 | 0.0746 | | Pittsburgh,39 | 464 | 26 | 438 | 0.0560 | | Fort Worth,45 | 376 | 26 | 350 | 0.0691 | | Alpharetta,11 | 261 | 25 | 236 | 0.0958 | | Colorado Springs,6 | 318 | 25 | 293 | 0.0786 | | columbus,36 | 399 | 25 | 374 | 0.0627 | | Sacramento,5 | 301 | 24 | 277 | 0.0797 | | beverly hills,5 | 195 | 23 | 172 | 0.1179 | | Cleveland,36 | 328 | 23 | 305 | 0.0701 | | Kansas City,26 | 286 | 23 | 263 | 0.0804 | | Hollywood,10 | 168 | 23 | 145 | 0.1369 | | Louisville,18 | 345 | 22 | 323 | 0.0638 | | Scottsdale,3 | 376 | 22 | 354 | 0.0585 | | Baltimore,21 | 261 | 21 | 240 | 0.0805 | | Cincinnati,36 | 444 | 21 | 423 | 0.0473 | | oklahoma city,37 | 352 | 20 | 332 | 0.0568 | | Kingwood,45 | 85 | 20 | 65 | 0.2353 | | Saint Louis,26 | 389 | 20 | 369 | 0.0514 | | Nashville,44 | 365 | 20 | 345 | 0.0548 | | Alexandria,49 | 351 | 20 | 331 | 0.0570 | | Saint Paul,24 | 392 | 19 | 373 | 0.0485 | | Knoxville,44 | 208 | 19 | 189 | 0.0913 | | Arlington,45 | 146 | 19 | 127 | 0.1301 | | Tucson,3 | 309 | 19 | 290 | 0.0615 | | Milwaukee,52 | 289 | 19 | 270 | 0.0657 | | Grand Rapids,23 | 179 | 19 | 160 | 0.1061 | | Albuquerque,32 | 274 | 18 | 256 | 0.0657 | | Saint Petersburg,10 | 141 | 18 | 123 | 0.1277 | | Long Beach,5 | 147 | 18 | 129 | 0.1224 | | Rochester,33 | 219 | 17 | 202 | 0.0776 | | Memphis,44 | 177 | 17 | 160 | 0.0960 | | Virginia Beach,49 | 331 | 17 | 314 | 0.0514 | | Jacksonville,10 | 301 | 17 | 284 | 0.0565 | | Walnut Creek,5 | 98 | 16 | 82 | 0.1633 | | Santa Rosa,5 | 71 | 16 | 55 | 0.2254 | | Newport Beach,5 | 159 | 16 | 143 | 0.1006 | | staten island,33 | 203 | 16 | 187 | 0.0788 | | Aurora,6 | 169 | 15 | 154 | 0.0888 | | Spring,45 | 259 | 14 | 245 | 0.0541 | | Bakersfield,5 | 134 | 14 | 120 | 0.1045 | | Omaha,28 | 310 | 14 | 296 | 0.0452 | | Riverside,5 | 116 | 14 | 102 | 0.1207 | | White Plains,33 | 103 | 14 | 89 | 0.1359 | | Boca Raton,10 | 157 | 14 | 143 | 0.0892 | | Frisco,45 | 168 | 14 | 154 | 0.0833 | | Oakland,5 | 127 | 14 | 113 | 0.1102 | | Tacoma,50 | 180 | 14 | 166 | 0.0778 | | Stamford,7 | 131 | 14 | 117 | 0.1069 | | Buffalo,33 | 258 | 14 | 244 | 0.0543 | | Henderson,29 | 110 | 14 | 96 | 0.1273 | | Columbia,42 | 129 | 14 | 115 | 0.1085 | | Reno,29 | 147 | 14 | 133 | 0.0952 | | Pompano Beach,10 | 166 | 14 | 152 | 0.0843 | | Chandler,3 | 155 | 13 | 142 | 0.0839 | | Madison,52 | 250 | 13 | 237 | 0.0520 | | Marietta,11 | 216 | 13 | 203 | 0.0602 | | Lexington,18 | 155 | 13 | 142 | 0.0839 | | Englewood,6 | 129 | 13 | 116 | 0.1008 | | Pasadena,5 | 135 | 13 | 122 | 0.0963 | | glendale,3 | 104 | 13 | 91 | 0.1250 | | Conroe,45 | 65 | 13 | 52 | 0.2000 | | Ann Arbor,23 | 144 | 13 | 131 | 0.0903 | | west palm beach,10 | 167 | 13 | 154 | 0.0778 | | Palo Alto,5 | 82 | 12 | 70 | 0.1463 | | Fredericksburg,49 | 115 | 12 | 103 | 0.1043 | | Fresno,5 | 114 | 12 | 102 | 0.1053 | | Bronx,33 | 153 | 12 | 141 | 0.0784 | | Fairfield,7 | 67 | 12 | 55 | 0.1791 | | naperville,14 | 161 | 12 | 149 | 0.0745 | +---------------------+--------------+--------+------+--------+ 100 rows in set (16.62 sec) mysql> select state, sum(case when gender between 1 and 2 then 1 else 0 end) as PAID_DELETES, sum(case when gender=1 then 1 else 0 end) as FEMALE, sum(case when gender=2 then 1 else 0 end) as MALE, sum(case when gender=1 then 1 else 0 end)/sum(case when gender between 1 and 2 then 1 else 0 end) as RATIO from us_am_am_member where first_name='' group by state order by FEMALE desc limit 100; +-------+--------------+--------+-------+--------+ | state | PAID_DELETES | FEMALE | MALE | RATIO | +-------+--------------+--------+-------+--------+ | 5 | 13547 | 1124 | 12423 | 0.0830 | | 45 | 10113 | 851 | 9262 | 0.0841 | | 33 | 8778 | 698 | 8080 | 0.0795 | | 10 | 6369 | 522 | 5847 | 0.0820 | | 31 | 4672 | 375 | 4297 | 0.0803 | | 14 | 4780 | 363 | 4417 | 0.0759 | | 39 | 4904 | 319 | 4585 | 0.0650 | | 11 | 3699 | 304 | 3395 | 0.0822 | | 49 | 4341 | 287 | 4054 | 0.0661 | | 22 | 3592 | 283 | 3309 | 0.0788 | | 36 | 3691 | 262 | 3429 | 0.0710 | | 34 | 3509 | 232 | 3277 | 0.0661 | | 23 | 2993 | 207 | 2786 | 0.0692 | | 6 | 2870 | 199 | 2671 | 0.0693 | | 21 | 2762 | 196 | 2566 | 0.0710 | | 7 | 1938 | 179 | 1759 | 0.0924 | | 50 | 2746 | 171 | 2575 | 0.0623 | | 3 | 2407 | 155 | 2252 | 0.0644 | | 44 | 1902 | 146 | 1756 | 0.0768 | | 15 | 1809 | 136 | 1673 | 0.0752 | | 52 | 1672 | 122 | 1550 | 0.0730 | | 26 | 1746 | 119 | 1627 | 0.0682 | | 24 | 2135 | 117 | 2018 | 0.0548 | | 42 | 1311 | 98 | 1213 | 0.0748 | | 1 | 1326 | 95 | 1231 | 0.0716 | | 38 | 1130 | 91 | 1039 | 0.0805 | | 46 | 1270 | 90 | 1180 | 0.0709 | | 18 | 1101 | 89 | 1012 | 0.0808 | | 29 | 853 | 79 | 774 | 0.0926 | | 19 | 1263 | 74 | 1189 | 0.0586 | | 37 | 1108 | 63 | 1045 | 0.0569 | | 9 | 751 | 62 | 689 | 0.0826 | | 16 | 820 | 60 | 760 | 0.0732 | | 17 | 1042 | 60 | 982 | 0.0576 | | 25 | 538 | 50 | 488 | 0.0929 | | 30 | 648 | 47 | 601 | 0.0725 | | 32 | 584 | 41 | 543 | 0.0702 | | 4 | 588 | 37 | 551 | 0.0629 | | 28 | 648 | 35 | 613 | 0.0540 | | 51 | 370 | 33 | 337 | 0.0892 | | 41 | 394 | 32 | 362 | 0.0812 | | 13 | 401 | 29 | 372 | 0.0723 | | 20 | 300 | 27 | 273 | 0.0900 | | 8 | 300 | 25 | 275 | 0.0833 | | 12 | 385 | 19 | 366 | 0.0494 | | 2 | 197 | 19 | 178 | 0.0964 | | 27 | 282 | 17 | 265 | 0.0603 | | 35 | 263 | 17 | 246 | 0.0646 | | 43 | 212 | 17 | 195 | 0.0802 | | 47 | 197 | 16 | 181 | 0.0812 | | 53 | 219 | 9 | 210 | 0.0411 | | 56 | 15 | 1 | 14 | 0.0667 | | 40 | 12 | 1 | 11 | 0.0833 | | 54 | 3 | 1 | 2 | 0.3333 | | 48 | 1 | 0 | 1 | 0.0000 | +-------+--------------+--------+-------+--------+ 55 rows in set (6 min 41.39 sec)