SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Results of an online poll

    Hello there, hope in your help.

    I need extract from my mysql table the results of an online poll.

    The questions:
    • I speak only English Yes No
    • I speak only Spanish Yes No
    • I speak English and a little bit of Spanish Yes No
    • I speak Spanish and a little bit of English Yes No
    • I speak English as well as Spanish Yes No

    I tried this query:
    Code:
    SELECT
    	SUM(
    
    		IF (
    			I_speak_only_English = "Yes",
    			1,
    			0
    		)
    	) AS `only_English`
    	COUNT(*) AS `total`
    FROM
    	tbl_poll
    GROUP BY
    	names
    ORDER BY
    	names DESC
    But how do we count the answer with "No"?
    Please tell me how to resolve this problem ...
    thank you

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    But how do we count the answer with "No"?
    perhaps with another IF

    please explain the purpose of your GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you for reply.
    you mean this?
    then two different columns, a column for response Yes and other for response No?
    GROUP BY is an my error...
    Code:
    SELECT
    	SUM(
    
    		IF (
    			I_speak_only_English = "Yes",
    			1,
    			0
    		)
    	) AS `only_English YES`,
    	SUM(
    
    		IF (
    			I_speak_only_English = "No",
    			1,
    			0
    		)
    	) AS `only_English NO,
    	COUNT(*) AS `total`
    FROM
    	tbl_poll
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you cannot use SUM without GROUP BY unless you want only a single row of results

    is that what you really want?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you for help.

    this is the output, but if you have other suggestions to improve the output are welcome...
    Code:
    mysql> SELECT
    	SUM(
    
    		IF (I_speak_only_English = "N", 1, 0)
    	) AS `I_speak_only_English NO`,
    	SUM(
    
    		IF (I_speak_only_English = "Y", 1, 0)
    	) AS `I_speak_only_English YES`,
    	COUNT(*) AS `total`
    FROM
    	tbl_poll;
    
    +-------------------------+--------------------------+-------+
    | I_speak_only_English NO | I_speak_only_English YES | total |
    +-------------------------+--------------------------+-------+
    | 16                      | 3                        |     19|
    +-------------------------+--------------------------+-------+
    1 row in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    ...but if you have other suggestions to improve the output are welcome...
    sure
    Code:
    SELECT I_speak_only_English
         , COUNT(*) AS total
      FROM tbl_poll
    GROUP
        BY I_speak_only_English
    this has been another service brought to you by http://stop-making-it-so-hard-on-yourself.com
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this has been another service brought to you by http://stop-making-it-so-hard-on-yourself.com


    thank you
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


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
  •