Performing a query on a result from another query?

Question

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

Answer 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 

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