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

Solution

Try this

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

  ) AS mtd

Comments

[Cyclonecode]

Show the entire query. Have you tried adding the DISTINCT right before the actual case i.e: COUNT(DISTINCT CASE ...)

[echo_Me]

this will count unique ColumnIDs and count them yes.

[7H3 IN5ID3R]

how about multiple case statements ?

[Crusaderpyro]

anyway to have more than 1 column in the DISTINCT ? like THEN ColumnID, AnotherID. It gives a parse exception because of the comma