count total records after groupBy select

I have a mysql select query that has a groupBy. I want to 开发者_Go百科count all the records after the group by statement. Is there a way for this directly from mysql ?

thanks.

If the only thing you need is the count after grouping, and you don't want to use 2 separate queries to find the answer. You can do it with a sub query like so...

select count(*) as `count`
from (
    select 0 as `doesn't matter`
    from `your_table` yt
    group by yt.groupfield
) sq

Note: You have to actually select something in the sub query, but what you select doesn't matter

Note: All temporary tables have to have a named alias, hence the "sq" at the end

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.

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