Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7

Thread: 3 queries in 1

  1. #1
    Regular Coder
    Join Date
    Apr 2007
    Posts
    123
    Thanks
    1
    Thanked 0 Times in 0 Posts

    3 queries in 1

    I want to run the following query:
    select the number of men and women who voted for each party in each US state. Can this be done in a single query? If so, whats the best way to approach it? Nested selects?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Depends on your database schema of course, but generally you should be able to GROUP BY party, selecting COUNT(*), to get your list of totals.

  • #3
    Regular Coder
    Join Date
    Apr 2007
    Posts
    123
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Using latest Wamp server, MyIsam engine. I was thinking of doing a main select on the party, then seperate select queries for each count or is there a more efficient approach?
    Basically the results will be in this format:
    party male female state

  • #4
    Regular Coder
    Join Date
    Apr 2011
    Posts
    286
    Thanks
    2
    Thanked 39 Times in 39 Posts
    Code:
    SELECT party, SUM(IF(gender=Male,1,0)) male, SUM(IF(gender=Female,1,0)) female, state FROM results GROUP BY party, state;

  • #5
    Regular Coder
    Join Date
    Apr 2007
    Posts
    123
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I tried your solution Wojjie as is in mysql console and it gave me unknown column Male. I tried it again with the Male and Female comparisons in single quotes, i.e Gender='Male' and Gender='Female' and it worked.
    One odd thing of note, I tried this query in phpmyadmin and it failed. This worked though, same output as mysql console
    SELECT party, SUM(gender='Male'))as Male, SUM(gender='Female')) as Female, state FROM results GROUP BY party, state;

    Thanks again.

  • #6
    Regular Coder
    Join Date
    Apr 2011
    Posts
    286
    Thanks
    2
    Thanked 39 Times in 39 Posts
    Your welcome, my example was more meant to be written in "english" not really meant to be used directly with SQL, but I am glad you managed to get it working.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,559
    Thanks
    80
    Thanked 4,492 Times in 4,456 Posts
    Quote Originally Posted by rpjd View Post
    One odd thing of note, I tried this query in phpmyadmin and it failed.
    I'd have to guess operator error. No reason it shouldn't work wherever used.

    This worked though, same output as mysql console
    SELECT party, SUM(gender='Male'))as Male, SUM(gender='Female')) as Female, state FROM results GROUP BY party, state;
    That works because the expressions gender='Male' and gender='Female' produce boolean values. But when you ask to get the SUM(), MySQL has to convert the boolean values to integers. And, not surprisingly, TRUE converts to 1 and FALSE converts to 0.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •