Can I divide the values in a column by the number of rows returned in the query, using a single query?
For example, I select some numeric value from a table based on some condition:
select value from table where ...
Let's say it returns N rows and I want the returned values divided by the number of rows returned:
select value / N from table where ...
It can be done with several queries (e.g. after the first query, I query the number of rows and do the query again). But can it be done in a single query with some SQL trick without query condition duplication, so that the actual selection (the WHERE part) which may be complicated runs only once?
You can do it in a single query, but as far as I know, with mysql you have to repeat the condition:
INNER JOIN (select @cnt = count(*)
FROM table1 WHERE [the same condition as in main query]) ON (1=1)
Or you can just
SELECT value/(SELECT COUNT(*) FROM table1 WHERE ...)
I believe optimizer should generate the same execution plan for both queries.