mercredi 27 mai 2015

Using SUM with CASE statement

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??

Aucun commentaire:

Enregistrer un commentaire