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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Performing a query on a result from another query?

Examples related to sql

Examples related to select

Examples related to count

Examples related to aggregate

Examples related to sum