Niall Niall - 2 months ago 18
MySQL Question

CONCAT With ROLLUP

I wanted to combine the first and last name columns while using a rollup but it seems to copy the same last name onto every firstname:

SELECT IFNULL(CONCAT(u.firstname, ' ', u.lastname),'Total Projects') AS Name, count(DISTINCT(p.id) ) AS Total
FROM projects p
INNER JOIN users u ON p.creator_fk = u.id
GROUP BY Name WITH ROLLUP;


So I end up with something like:

Name | Total

John Smith | 10
Sarah Smith | 25
Tracy Smith | 5
Total Projects | 40

Answer Source

As per my understanding

SELECT IFNULL(name,'Total Projects') AS Name, count(DISTINCT(id)) AS Total 
from  (select CONCAT(u.firstname,' ', u.lastname) as name,p.id
    FROM projects p INNER JOIN users u ON p.creator_fk = u.id) as p  GROUP BY name WITH ROLLUP;