Performing a query on a result from another query?
136,398
Solution 1
Usually you can plug a Query's result (which is basically a table) as the FROM clause source of another query, so something like this will be written:
SELECT COUNT(*), SUM(SUBQUERY.AGE) from
(
SELECT availables.bookdate AS Date, DATEDIFF(now(),availables.updated_at) as Age
FROM availables
INNER JOIN rooms
ON availables.room_id=rooms.id
WHERE availables.bookdate BETWEEN '2009-06-25' AND date_add('2009-06-25', INTERVAL 4 DAY) AND rooms.hostel_id = 5094
GROUP BY availables.bookdate
) AS SUBQUERY
Solution 2
You just wrap your query in another one:
SELECT COUNT(*), SUM(Age)
FROM (
SELECT availables.bookdate AS Count, DATEDIFF(now(),availables.updated_at) as Age
FROM availables
INNER JOIN rooms
ON availables.room_id=rooms.id
WHERE availables.bookdate BETWEEN '2009-06-25' AND date_add('2009-06-25', INTERVAL 4 DAY) AND rooms.hostel_id = 5094
GROUP BY availables.bookdate
) AS tmp;
Solution 3
I don't know if you even need to wrap it. Won't this work?
SELECT COUNT(*), SUM(DATEDIFF(now(),availables.updated_at))
FROM availables
INNER JOIN rooms ON availables.room_id=rooms.id
WHERE availables.bookdate BETWEEN '2009-06-25'
AND date_add('2009-06-25', INTERVAL 4 DAY)
AND rooms.hostel_id = 5094
GROUP BY availables.bookdate);
If your goal is to return both result sets then you'll need to store it some place temporarily.
Author by
holden
Updated on February 27, 2020Comments
-
holden about 4 years
I have a the query:
SELECT availables.bookdate AS Date, DATEDIFF(now(),availables.updated_at) as Age FROM availables INNER JOIN rooms ON availables.room_id=rooms.id WHERE availables.bookdate BETWEEN '2009-06-25' AND date_add('2009-06-25', INTERVAL 4 DAY) AND rooms.hostel_id = 5094 GROUP BY availables.bookdate
Which returns something like:
Date Age 2009-06-25 0 2009-06-26 2 2009-06-27 1 2009-06-28 0 2009-06-29 0
How can I then do a Count on the number of rows which is returned.. (in this case 5) and an SUM of the Ages? To return just one row with the Count and the SUM?
Count SUM 5 3
Thanks
-
steff_bdh over 8 yearsquestion, won't the sub query be run for each record of the outer query ? or will the result be cached
-
SWeko over 8 yearsWhat sql server will most likely do, is just run the subquery once, and then do a stream aggregate on the result. You can see what it actually did by using the
Include Actual Execution Plan
option.