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

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

Answer 2

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 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.

Answer 4

Note that your initial query is probably not returning what you want:

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

You are grouping by book date, but you are not using any grouping function on the second column of your query.

The query you are probably looking for is:

SELECT availables.bookdate AS Date, count(*) as subtotal, sum(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