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

`SELECT 10 / COUNT(*) FROM Table1 WHERE user = "carl" AND ans = "yes"`? — Sebas, Jan 21 '14 at 22:33

score 18 · Accepted Answer · answered Jan 21 '14 at 22:55

18

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

answered Jan 21 '14 at 22:55

Gordon Linoff

+1. Given the OP's specific queries this is an ideal solution. – Ike Walker Jan 21 '14 at 23:11

score 5 · Answer 2 · answered Jan 21 '14 at 22:40

5

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")

answered Jan 21 '14 at 22:40

Ike Walker

score 2 · Answer 3 · answered Jan 21 '14 at 22:35

2

Do a

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

Then use your myVariable as you need.

answered Jan 21 '14 at 22:35

Andre Polykanine

3 Answers3