Search code examples
mysqlcountdivide

divide by count result in mysql

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

Solution

  • 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';