...

View Full Version : 3 queries in 1



rpjd
04-18-2011, 09:08 PM
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?

Fumigator
04-18-2011, 09:12 PM
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.

rpjd
04-18-2011, 09:52 PM
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

Wojjie
04-18-2011, 09:58 PM
SELECT party, SUM(IF(gender=Male,1,0)) male, SUM(IF(gender=Female,1,0)) female, state FROM results GROUP BY party, state;

rpjd
04-18-2011, 10:39 PM
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.

Wojjie
04-18-2011, 10:45 PM
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.

Old Pedant
04-18-2011, 11:26 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum