count total records after groupBy select

Question

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.

Solution 1

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.

Solution 2

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

Solution 3

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

Comments

[sectus]

Do not forget about SQL_CALC_FOUND_ROWS after SELECT.

[Binar Web]

this is bad in any situation, especially when you have millions of records

[Marnix.hoh]

FOUND_ROWS() is deprecated: dev.mysql.com/doc/refman/8.0/en/…