Easy A
Would you like to react to this message? Create an account in a few clicks or log in to continue.


 
HomeHome  Latest imagesLatest images  SearchSearch  RegisterRegister  Log inLog in  

Share
 

 3 queries in 1

View previous topic View next topic Go down 
AuthorMessage
Twerk
Member
Member
Twerk

Posts : 26
Join date : 2011-04-17

3 queries in 1 Empty
PostSubject: 3 queries in 1   3 queries in 1 EmptyMon Apr 18, 2011 9:18 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?
Back to top Go down
Scars
Member
Member
avatar

Posts : 16
Join date : 2011-03-09

3 queries in 1 Empty
PostSubject: Re: 3 queries in 1   3 queries in 1 EmptyMon Apr 18, 2011 9:19 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.
Back to top Go down
Twerk
Member
Member
Twerk

Posts : 26
Join date : 2011-04-17

3 queries in 1 Empty
PostSubject: Re: 3 queries in 1   3 queries in 1 EmptyMon Apr 18, 2011 9:19 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
Back to top Go down
AllTheHits
Member
Member
avatar

Posts : 6
Join date : 2011-03-09

3 queries in 1 Empty
PostSubject: Re: 3 queries in 1   3 queries in 1 EmptyMon Apr 18, 2011 9:20 pm

Code:

SELECT party, SUM(IF(gender=Male,1,0)) male, SUM(IF(gender=Female,1,0)) female, state FROM results GROUP BY party, state;
Back to top Go down
Twerk
Member
Member
Twerk

Posts : 26
Join date : 2011-04-17

3 queries in 1 Empty
PostSubject: Re: 3 queries in 1   3 queries in 1 EmptyMon Apr 18, 2011 9:21 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.
Back to top Go down
AllTheHits
Member
Member
avatar

Posts : 6
Join date : 2011-03-09

3 queries in 1 Empty
PostSubject: Re: 3 queries in 1   3 queries in 1 EmptyMon Apr 18, 2011 10:13 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.
Back to top Go down
Sponsored content




3 queries in 1 Empty
PostSubject: Re: 3 queries in 1   3 queries in 1 Empty

Back to top Go down
 

3 queries in 1

View previous topic View next topic Back to top 
Page 1 of 1

Permissions in this forum:You cannot reply to topics in this forum
Easy A :: 3 queries in 1 Edit-trash Useless :: Trash-