+---------------------+--------------+--------+------+--------+ | CITYSTATE | PAID_DELETES | FEMALE | MALE | RATIO | +---------------------+--------------+--------+------+--------+ | New York,33 | 2121 | 196 | 1925 | 0.0924 | | Houston,45 | 1357 | 99 | 1258 | 0.0730 | | Chicago,14 | 1255 | 96 | 1159 | 0.0765 | | Los Angeles,5 | 925 | 81 | 844 | 0.0876 | | Dallas,45 | 693 | 81 | 612 | 0.1169 | | Atlanta,11 | 780 | 75 | 705 | 0.0962 | | Austin,45 | 705 | 62 | 643 | 0.0879 | | San Antonio,45 | 573 | 56 | 517 | 0.0977 | | Philadelphia,39 | 508 | 54 | 454 | 0.1063 | | Washington,9 | 642 | 52 | 590 | 0.0810 | | Denver,6 | 658 | 51 | 607 | 0.0775 | | Boston,22 | 560 | 51 | 509 | 0.0911 | | Brooklyn,33 | 560 | 47 | 513 | 0.0839 | | San Francisco,5 | 562 | 46 | 516 | 0.0819 | | San Diego,5 | 638 | 44 | 594 | 0.0690 | | Seattle,50 | 598 | 38 | 560 | 0.0635 | | Minneapolis,24 | 610 | 37 | 573 | 0.0607 | | Portland,38 | 407 | 37 | 370 | 0.0909 | | Charlotte,34 | 618 | 37 | 581 | 0.0599 | | Miami,10 | 461 | 36 | 425 | 0.0781 | | San Jose,5 | 331 | 35 | 296 | 0.1057 | | Las Vegas,29 | 350 | 34 | 316 | 0.0971 | | Indianapolis,15 | 364 | 33 | 331 | 0.0907 | | Fort Lauderdale,10 | 339 | 33 | 306 | 0.0973 | | Orlando,10 | 326 | 32 | 294 | 0.0982 | | Birmingham,1 | 281 | 32 | 249 | 0.1139 | | Phoenix,3 | 419 | 31 | 388 | 0.0740 | | Salt Lake City,46 | 346 | 29 | 317 | 0.0838 | | Plano,45 | 227 | 27 | 200 | 0.1189 | | Arlington,49 | 309 | 27 | 282 | 0.0874 | | Raleigh,34 | 309 | 26 | 283 | 0.0841 | | Tampa,10 | 364 | 26 | 338 | 0.0714 | | Columbus,36 | 348 | 25 | 323 | 0.0718 | | Alpharetta,11 | 236 | 25 | 211 | 0.1059 | | Colorado Springs,6 | 275 | 23 | 252 | 0.0836 | | Hollywood,10 | 151 | 22 | 129 | 0.1457 | | Fort Worth,45 | 299 | 22 | 277 | 0.0736 | | Pittsburgh,39 | 400 | 22 | 378 | 0.0550 | | Cleveland,36 | 281 | 21 | 260 | 0.0747 | | Kansas City,26 | 245 | 21 | 224 | 0.0857 | | Scottsdale,3 | 330 | 20 | 310 | 0.0606 | | Sacramento,5 | 250 | 20 | 230 | 0.0800 | | Kingwood,45 | 74 | 19 | 55 | 0.2568 | | Saint Louis,26 | 323 | 19 | 304 | 0.0588 | | Nashville,44 | 318 | 19 | 299 | 0.0597 | | Oklahoma City,37 | 275 | 19 | 256 | 0.0691 | | Louisville,18 | 296 | 19 | 277 | 0.0642 | | Beverly Hills,5 | 163 | 18 | 145 | 0.1104 | | Baltimore,21 | 219 | 18 | 201 | 0.0822 | | Arlington,45 | 117 | 18 | 99 | 0.1538 | | Knoxville,44 | 179 | 18 | 161 | 0.1006 | | Cincinnati,36 | 366 | 17 | 349 | 0.0464 | | Memphis,44 | 156 | 17 | 139 | 0.1090 | | Alexandria,49 | 297 | 17 | 280 | 0.0572 | | Rochester,33 | 186 | 17 | 169 | 0.0914 | | Albuquerque,32 | 232 | 16 | 216 | 0.0690 | | Saint Paul,24 | 324 | 16 | 308 | 0.0494 | | Walnut Creek,5 | 87 | 16 | 71 | 0.1839 | | Newport Beach,5 | 147 | 16 | 131 | 0.1088 | | Tucson,3 | 255 | 16 | 239 | 0.0627 | | Long Beach,5 | 126 | 16 | 110 | 0.1270 | | Staten Island,33 | 171 | 16 | 155 | 0.0936 | | Grand Rapids,23 | 151 | 16 | 135 | 0.1060 | | Jacksonville,10 | 250 | 15 | 235 | 0.0600 | | Saint Petersburg,10 | 121 | 15 | 106 | 0.1240 | | Pompano Beach,10 | 146 | 14 | 132 | 0.0959 | | Milwaukee,52 | 243 | 14 | 229 | 0.0576 | | Oakland,5 | 108 | 14 | 94 | 0.1296 | | Reno,29 | 121 | 14 | 107 | 0.1157 | | Buffalo,33 | 214 | 13 | 201 | 0.0607 | | Madison,52 | 210 | 13 | 197 | 0.0619 | | Englewood,6 | 117 | 13 | 104 | 0.1111 | | Henderson,29 | 89 | 13 | 76 | 0.1461 | | Columbia,42 | 116 | 13 | 103 | 0.1121 | | Aurora,6 | 137 | 13 | 124 | 0.0949 | | Marietta,11 | 199 | 13 | 186 | 0.0653 | | Spring,45 | 227 | 13 | 214 | 0.0573 | | Stamford,7 | 117 | 13 | 104 | 0.1111 | | White Plains,33 | 93 | 13 | 80 | 0.1398 | | Fairfield,7 | 58 | 12 | 46 | 0.2069 | | Tacoma,50 | 132 | 12 | 120 | 0.0909 | | Pasadena,5 | 120 | 12 | 108 | 0.1000 | | Palo Alto,5 | 78 | 12 | 66 | 0.1538 | | Conroe,45 | 54 | 12 | 42 | 0.2222 | | Frisco,45 | 155 | 12 | 143 | 0.0774 | | Bronx,33 | 133 | 12 | 121 | 0.0902 | | Bakersfield,5 | 106 | 12 | 94 | 0.1132 | | Omaha,28 | 262 | 12 | 250 | 0.0458 | | Virginia Beach,49 | 263 | 11 | 252 | 0.0418 | | Manassas,49 | 71 | 11 | 60 | 0.1549 | | Irving,45 | 92 | 11 | 81 | 0.1196 | | Chandler,3 | 142 | 11 | 131 | 0.0775 | | Glendale,3 | 85 | 11 | 74 | 0.1294 | | Naperville,14 | 149 | 11 | 138 | 0.0738 | | Lexington,18 | 120 | 11 | 109 | 0.0917 | | Fredericksburg,49 | 97 | 11 | 86 | 0.1134 | | Riverside,5 | 89 | 11 | 78 | 0.1236 | | Akron,36 | 74 | 11 | 63 | 0.1486 | | Fresno,5 | 93 | 11 | 82 | 0.1183 | | Denton,45 | 53 | 10 | 43 | 0.1887 | +---------------------+--------------+--------+------+--------+ 100 rows in set (9 min 50.25 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='' and dob between '1955-07-01' and '1985-07-01' group by state order by FEMALE desc limit 100; +-------+--------------+--------+-------+--------+ | state | PAID_DELETES | FEMALE | MALE | RATIO | +-------+--------------+--------+-------+--------+ | 5 | 11306 | 994 | 10312 | 0.0879 | | 45 | 8507 | 773 | 7734 | 0.0909 | | 33 | 7384 | 626 | 6758 | 0.0848 | | 10 | 5266 | 453 | 4813 | 0.0860 | | 31 | 4030 | 348 | 3682 | 0.0864 | | 14 | 4124 | 325 | 3799 | 0.0788 | | 39 | 4132 | 291 | 3841 | 0.0704 | | 11 | 3194 | 270 | 2924 | 0.0845 | | 22 | 3097 | 263 | 2834 | 0.0849 | | 49 | 3648 | 254 | 3394 | 0.0696 | | 36 | 3092 | 234 | 2858 | 0.0757 | | 34 | 2878 | 216 | 2662 | 0.0751 | | 6 | 2441 | 180 | 2261 | 0.0737 | | 21 | 2317 | 180 | 2137 | 0.0777 | | 23 | 2511 | 178 | 2333 | 0.0709 | | 7 | 1654 | 168 | 1486 | 0.1016 | | 50 | 2265 | 151 | 2114 | 0.0667 | | 3 | 2017 | 133 | 1884 | 0.0659 | | 44 | 1612 | 130 | 1482 | 0.0806 | | 15 | 1520 | 125 | 1395 | 0.0822 | | 52 | 1370 | 108 | 1262 | 0.0788 | | 26 | 1476 | 106 | 1370 | 0.0718 | | 24 | 1786 | 105 | 1681 | 0.0588 | | 1 | 1128 | 91 | 1037 | 0.0807 | | 42 | 1096 | 90 | 1006 | 0.0821 | | 38 | 928 | 78 | 850 | 0.0841 | | 18 | 920 | 77 | 843 | 0.0837 | | 29 | 691 | 71 | 620 | 0.1027 | | 46 | 1046 | 70 | 976 | 0.0669 | | 19 | 1049 | 70 | 979 | 0.0667 | | 16 | 698 | 53 | 645 | 0.0759 | | 37 | 903 | 53 | 850 | 0.0587 | | 9 | 645 | 52 | 593 | 0.0806 | | 17 | 867 | 51 | 816 | 0.0588 | | 25 | 450 | 46 | 404 | 0.1022 | | 30 | 556 | 41 | 515 | 0.0737 | | 32 | 465 | 34 | 431 | 0.0731 | | 51 | 311 | 32 | 279 | 0.1029 | | 41 | 331 | 31 | 300 | 0.0937 | | 4 | 492 | 31 | 461 | 0.0630 | | 28 | 529 | 28 | 501 | 0.0529 | | 20 | 249 | 26 | 223 | 0.1044 | | 13 | 337 | 24 | 313 | 0.0712 | | 8 | 253 | 22 | 231 | 0.0870 | | 12 | 277 | 17 | 260 | 0.0614 | | 47 | 161 | 16 | 145 | 0.0994 | | 43 | 175 | 15 | 160 | 0.0857 | | 2 | 142 | 14 | 128 | 0.0986 | | 27 | 223 | 14 | 209 | 0.0628 | | 35 | 204 | 13 | 191 | 0.0637 | | 53 | 174 | 7 | 167 | 0.0402 | | 54 | 3 | 1 | 2 | 0.3333 | | 40 | 12 | 1 | 11 | 0.0833 | | 56 | 12 | 1 | 11 | 0.0833 | | 48 | 1 | 0 | 1 | 0.0000 | +-------+--------------+--------+-------+--------+ mysql> select concat(round(latitude,0),",",round(longitude,0)) as LATLONG, city, 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='' and dob between '1955-07-01' and '1985-07-01' group by LATLONG order by FEMALE desc limit 100; +---------+------------------+-------+--------------+--------+------+--------+ | LATLONG | city | state | PAID_DELETES | FEMALE | MALE | RATIO | +---------+------------------+-------+--------------+--------+------+--------+ | 41,-74 | New York | 33 | 6721 | 576 | 6145 | 0.0857 | | 34,-118 | Marina Del Rey | 5 | 4166 | 349 | 3817 | 0.0838 | | 39,-77 | Mc Lean | 49 | 4084 | 301 | 3783 | 0.0737 | | 33,-97 | Plano | 45 | 2677 | 285 | 2392 | 0.1065 | | 42,-88 | Downers Grove | 14 | 3276 | 269 | 3007 | 0.0821 | | 42,-71 | Hudson | 22 | 2228 | 198 | 2030 | 0.0889 | | 40,-75 | Levittown | 39 | 2342 | 194 | 2148 | 0.0828 | | 34,-84 | Cumming | 11 | 1848 | 169 | 1679 | 0.0915 | | 38,-122 | San Francisco | 5 | 1713 | 164 | 1549 | 0.0957 | | 41,-73 | Port Jefferson | 33 | 1499 | 154 | 1345 | 0.1027 | | 26,-80 | Delray Beach | 10 | 1516 | 134 | 1382 | 0.0884 | | 30,-95 | La Porte | 45 | 1486 | 129 | 1357 | 0.0868 | | 40,-105 | Littleton | 6 | 1629 | 123 | 1506 | 0.0755 | | 37,-122 | Mountain View | 5 | 947 | 106 | 841 | 0.1119 | | 30,-98 | Austin | 45 | 1025 | 92 | 933 | 0.0898 | | 40,-74 | Allendale | 31 | 771 | 87 | 684 | 0.1128 | | 33,-117 | Chula Vista | 5 | 1222 | 80 | 1142 | 0.0655 | | 45,-93 | Lakeville | 24 | 1285 | 74 | 1211 | 0.0576 | | 30,-96 | Sugar Land | 45 | 1050 | 70 | 980 | 0.0667 | | 40,-76 | Wilmington | 8 | 888 | 66 | 822 | 0.0743 | | 34,-119 | Thousand Oaks | 5 | 691 | 65 | 626 | 0.0941 | | 48,-122 | Kirkland | 50 | 1214 | 63 | 1151 | 0.0519 | | 36,-79 | Fuquay Varina | 34 | 681 | 61 | 620 | 0.0896 | | 35,-81 | Matthews | 34 | 938 | 60 | 878 | 0.0640 | | 41,-82 | Millersburg | 36 | 663 | 58 | 605 | 0.0875 | | 43,-71 | Rochester | 30 | 821 | 57 | 764 | 0.0694 | | 42,-73 | Boston | 22 | 663 | 56 | 607 | 0.0845 | | 34,-112 | Phoenix | 3 | 770 | 55 | 715 | 0.0714 | | 40,-86 | Whiteland | 15 | 686 | 53 | 633 | 0.0773 | | 41,-75 | Annandale | 31 | 668 | 52 | 616 | 0.0778 | | 42,-72 | Amherst | 22 | 541 | 52 | 489 | 0.0961 | | 33,-112 | Phoenix | 3 | 811 | 51 | 760 | 0.0629 | | 36,-115 | Las Vegas | 29 | 475 | 50 | 425 | 0.1053 | | 41,-112 | Salt Lake City | 46 | 757 | 48 | 709 | 0.0634 | | 29,-81 | Winter Park | 10 | 488 | 47 | 441 | 0.0963 | | 43,-83 | Clarkston | 23 | 725 | 46 | 679 | 0.0634 | | 40,-83 | Columbus | 36 | 636 | 46 | 590 | 0.0723 | | 34,-117 | Murrieta | 5 | 498 | 44 | 454 | 0.0884 | | 28,-83 | Port Richey | 10 | 548 | 43 | 505 | 0.0785 | | 39,-121 | Roseville | 5 | 460 | 42 | 418 | 0.0913 | | 34,-85 | Fairmount | 11 | 491 | 41 | 450 | 0.0835 | | 42,-83 | Lincoln Park | 23 | 572 | 40 | 532 | 0.0699 | | 39,-95 | Gardner | 17 | 724 | 40 | 684 | 0.0552 | | 36,-87 | Franklin | 44 | 609 | 40 | 569 | 0.0657 | | 43,-88 | Kenosha | 52 | 579 | 40 | 539 | 0.0691 | | 28,-82 | Land O Lakes | 10 | 483 | 40 | 443 | 0.0828 | | 39,-78 | Purcellville | 49 | 374 | 38 | 336 | 0.1016 | | 27,-80 | Stuart | 10 | 434 | 37 | 397 | 0.0853 | | 45,-123 | Beaverton | 38 | 375 | 35 | 340 | 0.0933 | | 39,-105 | Colorado Springs | 6 | 432 | 35 | 397 | 0.0810 | | 47,-122 | Bonney Lake | 50 | 364 | 33 | 331 | 0.0907 | | 40,-80 | Pittsburgh | 39 | 563 | 32 | 531 | 0.0568 | | 30,-82 | Jacksonville | 10 | 343 | 31 | 312 | 0.0904 | | 39,-90 | Saint Louis | 26 | 531 | 31 | 500 | 0.0584 | | 38,-86 | Louisville | 18 | 387 | 30 | 357 | 0.0775 | | 46,-123 | Vancouver | 50 | 416 | 30 | 386 | 0.0721 | | 43,-74 | Rensselaer | 33 | 395 | 29 | 366 | 0.0734 | | 40,-77 | Thurmont | 21 | 493 | 29 | 464 | 0.0588 | | 42,-84 | Perrysburg | 36 | 397 | 28 | 369 | 0.0705 | | 28,-81 | Merritt Island | 10 | 255 | 28 | 227 | 0.1098 | | 43,-86 | Grand Rapids | 23 | 256 | 27 | 229 | 0.1055 | | 33,-87 | Birmingham | 1 | 266 | 27 | 239 | 0.1015 | | 38,-123 | Santa Rosa | 5 | 249 | 25 | 224 | 0.1004 | | 31,-98 | Round Rock | 45 | 234 | 25 | 209 | 0.1068 | | 30,-90 | Kenner | 19 | 366 | 25 | 341 | 0.0683 | | 41,-81 | Stow | 36 | 317 | 23 | 294 | 0.0726 | | 37,-76 | Norfolk | 49 | 546 | 23 | 523 | 0.0421 | | 35,-107 | Albuquerque | 32 | 267 | 22 | 245 | 0.0824 | | 39,-85 | Cincinnati | 36 | 352 | 22 | 330 | 0.0625 | | 30,-99 | San Antonio | 45 | 210 | 22 | 188 | 0.1048 | | 35,-90 | Memphis | 44 | 292 | 22 | 270 | 0.0753 | | 39,-91 | Ballwin | 26 | 242 | 21 | 221 | 0.0868 | | 36,-84 | Lenoir City | 44 | 233 | 21 | 212 | 0.0901 | | 36,-80 | Winston Salem | 34 | 315 | 21 | 294 | 0.0667 | | 34,-81 | West Columbia | 42 | 221 | 21 | 200 | 0.0950 | | 42,-94 | Urbandale | 16 | 254 | 21 | 233 | 0.0827 | | 42,-74 | Rhinebeck | 33 | 229 | 20 | 209 | 0.0873 | | 43,-78 | Lockport | 33 | 245 | 20 | 225 | 0.0816 | | 43,-79 | Buffalo | 33 | 358 | 20 | 338 | 0.0559 | | 43,-89 | Beloit | 52 | 290 | 20 | 270 | 0.0690 | | 39,-84 | Alexandria | 18 | 419 | 20 | 399 | 0.0477 | | 43,-76 | Fayetteville | 33 | 217 | 20 | 197 | 0.0922 | | 38,-121 | Sierra Foothills | 5 | 232 | 19 | 213 | 0.0819 | | 37,-120 | Clovis | 5 | 168 | 19 | 149 | 0.1131 | | 32,-111 | Tucson | 3 | 296 | 19 | 277 | 0.0642 | | 35,-82 | Greenville | 42 | 267 | 19 | 248 | 0.0712 | | 35,-79 | Fayetteville | 34 | 250 | 19 | 231 | 0.0760 | | 43,-72 | Concord | 30 | 239 | 19 | 220 | 0.0795 | | 40,-84 | Middletown | 36 | 267 | 19 | 248 | 0.0712 | | 39,-76 | Brooklyn | 21 | 240 | 18 | 222 | 0.0750 | | 47,-123 | Olympia | 50 | 208 | 18 | 190 | 0.0865 | | 38,-77 | Fredericksburg | 49 | 308 | 18 | 290 | 0.0584 | | 32,-90 | Ridgeland | 25 | 133 | 18 | 115 | 0.1353 | | 41,-76 | Allentown | 39 | 234 | 18 | 216 | 0.0769 | | 35,-85 | Dalton | 11 | 166 | 18 | 148 | 0.1084 | | 27,-82 | Cape Coral | 10 | 239 | 17 | 222 | 0.0711 | | 41,-80 | Natrona Heights | 39 | 313 | 17 | 296 | 0.0543 | | 44,-70 | Portland | 20 | 145 | 17 | 128 | 0.1172 | | 39,-94 | Warrensburg | 26 | 226 | 16 | 210 | 0.0708 | | 41,-96 | Omaha | 28 | 337 | 16 | 321 | 0.0475 | +---------+------------------+-------+--------------+--------+------+--------+ 100 rows in set (10 min 11.62 sec)