Sunday, March 1

MySql : Get the count of records from a table with Group By clause

While doing pagination i wanted to get the count of all the records to get the total number of pages. However since i am displaying the records grouping them via date parameter the total number of records will be affected.

Scenario : I am storing user records date wise. So on a particular date i could have entered more than 1 record. While displaying data, important is that i get the total records on date field.  The best option is to use a group by to get all the entries on a date, followed by a count on that query.

See MySql statements below.

Test 1 : Get all the records group by date for a particular user

SELECT count(*) as totalcount 
FROM task_details 
WHERE task_details.users_id = '7' 
GROUP BY DATE(task_details.date)

The above query returns total number of entries that are made a on a particular date for a particular user.

To get the total entries the user made date wise, we could call a select count(*) on the above query. For example see below

SELECT count(totalcount) as total 
FROM (
    SELECT count(*) as totalcount 
    FROM task_details 
    WHERE task_details.users_id = '7' GROUP BY DATE(task_details.date)
) as results

The above query returns the total entries (count) by user date wise.

Please note the use of "results" which is an alias to the subquery query. If we do not give any alias then MySql returns an error. Ref : Stackoverflow question here.

Be the first one to Comment!!!

Post a Comment