Results of an online poll

Databases
1

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

2

perhaps with another IF

please explain the purpose of your GROUP BY

3

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… :rolleyes:

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
4

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

is that what you really want?

5

thank you for help.

this is the output, but if you have other suggestions to improve the output are welcome… :slight_smile:

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
6

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

7

:lol:

thank you