divide by count result in mysql

mysql count divide

sleepsleepsleep90731 · Jan 21, 2014 · Viewed 15.2k times · Source

SELECT COUNT(*) FROM Table1 WHERE user = "carl" AND ans = "yes"

then i want to divide the output of this query to another query, for example the output is 10. so it will be like:

10 / SELECT COUNT(*) From Table1 WHERE user = "carl"

How is the right syntax for this?

Thank You

Answer

You want to use conditional aggregation and division. You don't need two queries:

SELECT SUM(ans = 'yes')/COUNT(*)
FROM Table1
WHERE user = 'carl';

The SUM(ans = 'yes') counts the number of rows with yes. Actually, you could further simplify this to:

SELECT avg(ans = 'yes')
FROM Table1
WHERE user = 'carl';