MySQL COUNT(CASE WHEN ... THEN DISTINCT Column)

Question

My query:

COUNT(
    CASE WHEN YEAR(FieldValue) = YEAR(CURDATE()) 
    AND MONTH(FieldValue) = MONTH(CURDATE())
    THEN 1
    END
) AS mtd

I want something like:

COUNT(
    CASE WHEN YEAR(FieldValue) = YEAR(CURDATE()) 
    AND MONTH(FieldValue) = MONTH(CURDATE())
    THEN DISTINCT ColumnID
    END
) AS mtd

This gives an error. I can not use GROUP BY. If I add DISTINCT before CASE it does not work either it treats FieldValue column as DISTINCT but I want it to count ColumnID as distinct.

Please help

Answer 1

Try this

  COUNT(DISTINCT(
    CASE WHEN YEAR(FieldValue) = YEAR(CURDATE()) 
    AND MONTH(FieldValue) = MONTH(CURDATE())
    THEN ColumnID 
    END )

  ) AS mtd