Why group_concat and SQL_CALC_FOUND_ROWS do not work with each other? I have following query which contain group_concat and SQL_CALC_FOUND_ROWS together as I want result + total no. of records present in it.

SELECT SQL_CALC_FOUND_ROWS GROUP_CONCAT(ca.id) 
FROM notification AS ca 
LEFT JOIN alert_msg AS an ON an.id=ca.alert_msg_id
WHERE ca.mem_id='777 ' 
AND  ca.date!="" 
AND  ca.alert_msg_id 
NOT IN(26,27) AND ca.from_id != '777'

SELECT FOUND_ROWS();


But this does not return the desired result.

Please suggest.
From the question itself, I see two problems


The return value of the query will be, at most, one row. SQL_CALC_FOUND_ROWS is uesless
You are looking for the number of rows that piled up in the GROUP_CONCAT result


You have to extrapolate the row count from the result of the group_concat. Here is how


If there is a return value from the GROUP_CONCAT, set @found_rows = 1 + the number of commas in the return value.
If no value is returned for the GROUP_CONCAT, set @found_rows = 0


BTW there is a heads you need to be aware of: GROUP_CONCAT's max length is 1024 by default. You should extend it to 10M.

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(ca.id)
INTO @IDList
FROM notification AS ca 
LEFT JOIN alert_msg AS an ON an.id=ca.alert_msg_id
WHERE ca.mem_id='777 ' 
AND  ca.date!="" 
AND  ca.alert_msg_id 
NOT IN(26,27) AND ca.from_id != '777'

SET @found_rows = IF(
    LOCATE(',',IFNULL(@IDList,''))=0,0,
    LENGTH(@IDList) - LENGTH(REPLACE(@IDList,',','')) + 1
);

SELECT @found_rows,@IDList;


Give it a Try !!!