Datatable serverside show count result of column with spesific values on footer

Question

How to make total row on footer like this with serverside DataTable

| Name  | Boy  | Girl |
| Boy1  |  -   |      |
| Girl1 |      |  -   |
| Boy2  |  -   |      |
| Total |  2   |  1   |

I use this query but the result only show 1 data (it should be 3 data for example)

select name, sum(case when sex = "male" then 1 else 0 end) as boy, sum(case when sex = "female" then 1 else 0 end) as girl' from student;

Thank you

Answer 1

If your database supports ROLLUP functionality, then you can try the following:

SELECT
    name,
    SUM(CASE WHEN sex = "male"   THEN 1 ELSE 0 END) AS boy,
    SUM(CASE WHEN sex = "female" THEN 1 ELSE 0 END) AS girl
FROM student
GROUP BY name
WITH ROLLUP

If you database does not support ROLLUP, then can use a UNION to add a record on the bottom for the total:

SELECT
    name,
    SUM(CASE WHEN sex = "male"   THEN 1 ELSE 0 END) AS boy,
    SUM(CASE WHEN sex = "female" THEN 1 ELSE 0 END) AS girl
FROM student
GROUP BY name
UNION ALL
SELECT
    'total',
    SUM(CASE WHEN sex = "male"   THEN 1 ELSE 0 END),
    SUM(CASE WHEN sex = "female" THEN 1 ELSE 0 END)
FROM student
ORDER BY
    CASE WHEN name = 'total' THEN 1 ELSE 0 END,
    name