Results of an online poll

[cms9651]

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:

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

[r937]

But how do we count the answer with "No"?

perhaps with another IF

please explain the purpose of your GROUP BY

[cms9651]

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...

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

[r937]

you cannot use SUM without GROUP BY unless you want only a single row of results

is that what you really want?

[cms9651]

thank you for help.

this is the output, but if you have other suggestions to improve the output are welcome...

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

[r937]

...but if you have other suggestions to improve the output are welcome...

sure

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

[cms9651]

this has been another service brought to you by http://stop-making-it-so-hard-on-yourself.com

thank you