DataTables with SQL query using count(), MAX() & GROUP BY

agaisab

Hi Allan,

I am currently in my trial version of DataTables Editor and it's been great so far! I am still trying to learn the system/library and cannot find information regarding whether the server-side library allows sql queries to include count(), MAX() and Group By. I am trying to populate my DataTables table using the following query.

$sql='SELECT a.id, a.Company, a.FirstName, a.LastName, a.Enrollment AS Employer_Registration, b.Managers, c.Referrals, c.Last_Enrollment AS Employee_Enrollment
FROM users a
LEFT JOIN (
SELECT Manager, count() AS Managers
FROM users
GROUP BY Manager
) b ON b.Manager = a.id
LEFT JOIN (
SELECT Referral, MAX(Enrollment) AS Last_Enrollment, count() AS Referrals
FROM enrolled_courses
GROUP BY Referral
ORDER BY Last_Enrollment
) c ON c.Referral = a.id
WHERE a.CRM_Type="parent"
ORDER BY a.Company';

Please let me know if you have any questions regarding what I am trying to accomplish.

Thank you in advance for your help!

Mike