divide by count result in mysql

Question

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 1

You can do this by running the two queries as sub-queries:

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

Answer 2

Do a

SELECT (10/COUNT(*)) AS `myVariable` FROM `Table1` WHERE...

Then use your myVariable as you need.

Answer 3

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