Total No of Result when using when using the Group by Clause

pankajbansal

Hi,

I am using the group by clause with datatable query in Codeigniter (DataTable Library). I am getting the correct data only the problem is I am not getting the correct no of Total Records in "Showing 1 to 15 of 15 entries (filtered from 15 total entries)".

The Query for getting the records from the database in datatable get_display_result() is:

SELECT * FROM (rpt_survey_end_of_month) WHERE MONTH(date_end) = '1' AND YEAR(date_end) = '2014' GROUP BY act_id ORDER BY act_id asc LIMIT 10

The query generated datatable get_total_results() method is :

SELECT COUNT(*) AS numrows FROM (rpt_survey_end_of_month) WHERE MONTH(date_end) = '1' AND YEAR(date_end) = '2014'

this is not taking the group by clause.
Any suggestion to solve this problem