SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Select in one statement

    Hey,

    I have used multiple SQL queries before but dont quite know how to do this one.

    Please excuse the way the database tables have been put together. You will see this with what i am trying to do..

    Right, so I have the following SQL query which works:-

    PHP Code:
                $query "SELECT COUNT(vote) AS F_1 FROM votes WHERE constituency_id = 1 AND vote = 'for'";
                
    $result =  mysql_query ($query);
                
    $numrows mysql_num_rows($result); 
    But i need to add the following query

    PHP Code:
    "SELECT COUNT(vote) AS F_2 FROM votes WHERE constituency = 1 AND vote = 'against'" 
    How can i add this to the same query?

    thanks

  2. #2
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    can anybody help me with this, i basically want to combine the 2 queries together...

  3. #3
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    combine 2 SQL statements in one

    Hey, how can i get the result of 2 SQL statements in the same query?

    I have the code below:-

    PHP Code:
                $query "SELECT COUNT(vote) AS F_1 FROM votes WHERE constituency_id = 1 AND vote = 'for'"
    But i also need to add the result of the following query aswell,

    PHP Code:
                $query "SELECT COUNT(vote) AS F_2 FROM votes WHERE constituency_id = 1 AND vote = 'against'"
    How can i combine these together?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    there are several ways

    here's how to get exactly what you asked for:
    Code:
    SELECT COUNT(CASE WHEN vote = 'for'
                      THEN 1 ELSE NULL END) AS F_1 
         , COUNT(CASE WHEN vote = 'against'
                      THEN 1 ELSE NULL END) AS F_2 
      FROM votes 
     WHERE constituency_id = 1 
       AND vote IN ( 'for', 'against' )
    and here's a simpler way to get the same thing:
    Code:
    SELECT vote
         , COUNT(*) AS total
      FROM votes 
     WHERE constituency_id = 1 
       AND vote IN ( 'for', 'against' )
    GROUP
        BY vote
    and finally, here's a more robust solution:
    Code:
    SELECT vote
         , COUNT(*) AS total
      FROM votes 
     WHERE constituency_id = 1 
    GROUP
        BY vote
    it's more robust because it produces counts for vote='abstain' as well

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    Thanks alot for your reply, it helps the code to work, however my SQL statement will be more complicated..

    PHP Code:
    $query "SELECT COUNT(CASE WHEN vote = 'for' THEN 1 ELSE NULL END) AS F_1, COUNT(CASE WHEN vote = 'against' THEN 1 ELSE NULL END) AS F_2 FROM votes WHERE constituency_id = 1 AND vote IN ('for', 'against')"
    Can i join this code below to the SQL query above?

    PHP Code:
    $query "SELECT COUNT(CASE WHEN vote = 'for' THEN 1 ELSE NULL END) AS [B] F_3[/B], COUNT(CASE WHEN vote = 'against' THEN 1 ELSE NULL END) AS [B]F_4[/B] FROM votes WHERE [B]constituency_id = 2 [/B] AND vote IN ('for', 'against')"
    Note the only thing that has changed is the constituency_id and bold parts..Can i put this in the statement aswell?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, you can combine them

    you should be detecting a pattern here...
    Code:
    SELECT constituency_id 
         , vote
         , COUNT(*) AS total
      FROM votes 
    GROUP
        BY constituency_id 
         , vote
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select sum(case when vote = 'For' then 1 else 0 end) as votesFor,
           sum(case when vote = 'Against' then 1 else 0 end) as votesAgainst
      from votes 
     where constituency = 1
       and vote in ('For','Against')

  8. #8
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    Thanks that works perfectly, i can see a pattern here, but what if wanted to add several constituencies?

    For exmaple at the moment i have this:-

    PHP Code:
    $query "select sum(case when vote = 'for' then 1 else 0 end) as votesFor, sum(case when vote = 'against' then 1 else 0 end) as votesAgainst from votes where constituency = '1' and vote in ('for','against')"
    But what would change if i wanted to add other constituency_id's?

    PHP Code:
    where constituency '2'
    where constituency '3'
    where constituency '4' 
    At the moment votesFor and votesAgainst is showing for just id '1', so would there be a way where i coould say votesFor_1. votesAgainst_1, votesFor_2, votesAgainst_2 and so on...

    I dont quite know how to include this in the SQL statement?

  9. #9
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    Thanks alot for this i can see what you are trying to get across, so the code that you have suggested, can i read out a specific constituency_id with the 'for' and 'against' field. I want the results to be like this:-

    http://www.listentotheconstituents.com/vote_summary.php

    Look for the 'Votes per Constituency' table..

    I am currently reading the field out like so:-

    PHP Code:
                <?php            
                 
    include("conn.php"); 

                
    $query "SELECT constituency_id, vote, COUNT(*) AS total FROM votes GROUP BY constituency_id, vote";
                
    $result =  mysql_query ($query);
                
    $numrows mysql_num_rows($result);
                    
                echo 
    "<table class='sofT' cellspacing='5' style='width:665px'>";
            
                echo 
    "<tr><td class='sup' colspan='1'><b>Constituency</b></td><td class='sup'><b>For</b></td><td class='sup'><b>Against</b></td></tr>";

                
    $row mysql_fetch_array($result,MYSQL_ASSOC);
                
                echo 
    "<tr><td>Bury North - David Chaytor, Labour (ID 1)</td><td>[B]$row[total][/B]</td><td>[B]$row[total][/B]</td></tr>";
                echo 
    "<tr><td>Bury South - Ivan Lewis, Labour (ID 2)</td><td>0</td><td>0</td></tr>";
                echo 
    "<tr><td>Cheadle - Mark Hunter, Liberal Democrat (ID 2)</td><td>0</td><td>0</td></tr>";
                echo 
    "<tr><td>Macclesfield - Sir Nicholas Winterton, Conservative (ID 2)</td><td>0</td><td>0</td></tr>";
                echo 
    "<tr><td>Salford - Right Honourable Hazel Blears, Labour (ID 2)</td><td>0</td><td>0</td></tr>";
                echo 
    "<tr><td>Tatton - George Osborne, Conservative (ID 2)</td><td>0</td><td>0</td></tr>";
                
                echo 
    "</table>";
                
    ?>

  10. #10
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,025
    Mentioned
    211 Post(s)
    Tagged
    1 Thread(s)
    Threads merged as they were related to the same problem

  11. #11
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    can anyone help me with this, i cant seem to find a way to pull out all of the values i need..

    Thanks

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    see post #6 -- all the values you need are there
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    Just one question, how does the query in the SQL statement in port #6 include both vote "for" and "against".??

    The line COUNT(*) AS total, counts everything as total so when i want to display the results woulg i do simething like $row[total]..?

    Regards

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by billy_111 View Post
    Just one question, how does the query in the SQL statement in port #6 include both vote "for" and "against".??
    run the query outside of php, inspect the results, and you will see!!!
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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