COUNT in GROUP_CONCAT not working while querying a dynamic column table

Question

I have a table named table1 like this:

   id    period         value       name
   1     2020-06-01     3           anna
   2     2020-06-01     2           anna
   3     2020-06-01     3           anna
   4     2020-06-01     1           juned
   5     2020-06-01     3           juned
   6     2020-06-01     2           juned
   7     2020-07-01     3           anna
   8     2020-07-01     2           anna
   9     2020-07-01     2           anna
  10     2020-07-01     3           juned
  11     2020-07-01     3           juned
  12     2020-07-01     3           juned

I expected the result of query show the count of value 3

name      2020-06-01     2020-07-01
anna          2              2
juned         1              3

I've tried this is the code, but it result error #1111 - invalid use of group function

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(IF (table1.period = "',
            table1.period, '", COUNT(CASE WHEN value = 3 THEN 1 END), 0)) AS `',
            table1.period, '`'
        )
    ) INTO @sql
FROM table1;
SET @sql =  CONCAT(
                'SELECT name, ',
                @sql,
                ' FROM table1 GROUP BY name'
            ); 
PREPARE stmt FROM @sql;
EXECUTE stmt;

Answer 1

I would write the dynamic SQL as:

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT 'SUM(value = 3 AND period = ''', period, ''') AS `', period, '`')
INTO @sql
FROM table1;

SET @sql =  CONCAT('SELECT name, ', @sql, ' FROM table1 GROUP BY name'); 
          
-- debug
select @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The main problem with your code is the way you phrased the conditional expressions within the aggregate functions. Also:

• you don't need CONCAT() inside GROUP_CONCAT(): you can put several values within GROUP_CONCAT(), separated by strings

• use single quotes rather than double quotes for literal strings

• DEALLOCATE the statement handler once the query is executed

Demo on DB Fiddle - with credits to Meet Soni for creating the fiddle in the first place.

Generated sql:

| @sql                                                                                                                                                     |
| :------------------------------------------------------------------------------------------------------------------------------------------------------- |
| SELECT name, SUM(value = 3 AND period = '2020-06-01') AS `2020-06-01`,SUM(value = 3 AND period = '2020-07-01') AS `2020-07-01` FROM table1 GROUP BY name |

Query results:

name  | 2020-06-01 | 2020-07-01
:---- | ---------: | ---------:
anna  |          2 |          1
juned |          1 |          3