CODEWITHSUNDEEP
mysqlsql
mike44
mike44

Reputation: 812

MySql count of group by result

I'm trying to get records based on applied search criteria. At a time I'm only selecting 50 rows(for pagination of the table used on webpage). But I also need the total number of rows that satisfies the search criteria (to show 1-50 out of <total> records & so on).

Following query gives me records group by userid.

select * from user 
group by userid
where name like '%hhh%' 
limit 50 offset 0

Here I'll will get first 50 records that satisfies the criteria, but total search results can be greater than 50. I need this total count along with other query result.

Upvotes: 0

Views: 1312

Answers (3)

tjmw
tjmw

Reputation: 309

MySQL supports this with the SQL_CALC_FOUND_ROWS option. Something like the following: (adapted (but untested!) from the docs):

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM user
-> WHERE name like '%hhh%' LIMIT 50;
mysql> SELECT FOUND_ROWS();

Upvotes: 0

Muhammad Saqlain Arif
Muhammad Saqlain Arif

Reputation: 544

try this query..

select count(*) as total,userid from users
where name like '%hhh%' 
 group by userid limit 50 offset 0

Upvotes: 0

laurie
laurie

Reputation: 708

I'd use a separate query:

select count(*) from user
group by userid
where name like '%hhh%'

Your query will be much quicker and can be run each time another 50 rows are selected, then you can run this longer running query once, to get the total

Upvotes: 2

Related Questions