Using SUM with CASE statement

Question

I'm trying to process the following query:

    SELECT TN.name_type_ne, WEEK(HC.date_check,1) AS date_week, YEAR(HC.date_check) AS date_year,
    SUM(IF(id_check > total_check_week, total_check_week, COUNT(id_check))) AS unique_check
    FROM HEALTH_CHECK HC
    LEFT JOIN TYPE_NE TN ON (HC.fk_id_type_ne = TN.id_type_ne AND HC.fk_id_ne IS NULL)
    LEFT JOIN CATEGORY_NE CN ON (TN.fk_id_category_ne = CN.id_category_ne)
    WHERE CN.name_category_ne = "R4"
    GROUP BY TN.name_type_ne, date_week, date_year

It seems it throws an error for the next select :

SUM(IF(id_check > total_check_week, total_check_week, COUNT(id_check))) AS unique_check

I also tried to replace it with :

SUM(CASE WHEN id_check > total_check_week THEN total_check_week ELSE COUNT(id_check) END) AS unique_check

But I keep having this Mysql error :

#1111 - Invalid use of group function

What is wrong with the query??

Answer 1

I finally found the solution. In my case, I was trying to use an aggregate function (COUNT) inside the SUM. It seems MySQL doesn't allow this practice.

Here is a solution with derived table:

 `SELECT SUM(unique_check) FROM
    (SELECT TN.name_type_ne, WEEK( HC.date_check, 1 ) AS date_week, YEAR( HC.date_check ) AS date_year,
    CASE WHEN COUNT(id_check) > total_check_week THEN total_check_week ELSE COUNT(id_check) END unique_check
    FROM HEALTH_CHECK HC
    LEFT JOIN TYPE_NE TN ON (HC.fk_id_type_ne = TN.id_type_ne AND HC.fk_id_ne IS NULL)
    LEFT JOIN CATEGORY_NE CN ON (TN.fk_id_category_ne = CN.id_category_ne)
    WHERE CN.name_category_ne = "R4"
    GROUP BY TN.name_type_ne, date_week, date_year) As T`