count total records after groupBy select

Question

Hello, I have a mysql select query that has a groupBy. I want to count all the records after the group by statement. Is there a way for this directly from mysql ?

thanks.

Answer 1

see this query for examples:

This query is used to find the available rooms record for a hotel, just check this

SELECT a.type_id, a.type_name, a.no_of_rooms,
       (SELECT SUM(booked_rooms) FROM reservation
       WHERE room_type = a.type_id
       AND start_date >= '2010-04-12'
       AND end_date <= '2010-04-15') AS booked_rooms,
       (a.no_of_rooms - (SELECT SUM(booked_rooms)
                  FROM reservation
              WHERE room_type = a.type_id
              AND start_date >= '2010-04-12'
              AND end_date <= '2010-04-15')) AS freerooms
FROM room_type AS a
LEFT JOIN reservation AS b
ON a.type_id = b.room_type
GROUP BY a.type_id ORDER BY a.type_id

Answer 2

You can use FOUND_ROWS():

SELECT <your_complicated_query>;
SELECT FOUND_ROWS();

It's really intended for use with LIMIT, telling you how many rows would have been returned without the LIMIT, but it seems to work just fine for queries that don't use LIMIT.