Dividing values in a column by the number of rows returned in the query. Can it be done in a single query?

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?

-------------Problems Reply------------

You can do it in a single query, but as far as I know, with mysql you have to repeat the condition:

value/@cnt from
INNER JOIN (select @cnt = count(*)
FROM table1 WHERE [the same condition as in main query]) ON (1=1)
WHERE condition

Or you can just

SELECT value/(SELECT COUNT(*) FROM table1 WHERE ...)
FROM table1

I believe optimizer should generate the same execution plan for both queries.

Category:mysql Views:1 Time:2012-02-06
Tags: mysql

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.100 (s). 11 q(s)