kez kez - 1 month ago 6
MySQL Question

select query result filter using if conditions

I have folllowing select query

SELECT
Table.ID
SUM(CASE WHEN Table.Status = 1 THEN 1 ELSE null END) AS NormalCount,
SUM(CASE WHEN Table.status = 2 THEN 1 ELSE null END) AS AbnormalCount
FROM Table
GROUP BY Table.ID


I want to get above results and generate new result set with following conditions

IF(NormalCount > 0 or AbnormalCount == NULL)
SELECT
Table.ID
Table.Status AS "Normal"
FROM Table
GROUP BY Table.ID

ELSE IF ( AbnormalCount > 0)
SELECT
Table.ID
Table.Status AS "Abnormal"
SUM(CASE WHEN Header.status = 2 THEN 1 ELSE null END) AS AbnormalCount
FROM Table
GROUP BY Table.ID

Answer

I think the logic you want is to label each ID group as being abnormal if it has one or more abnormal observation. If so, then you can use another CASE statement to check the conditional abnormal sum and label the status appropriately. Normal groups would have the characteristic of having an abnormal count of zero, but this count would appear for all groups.

SELECT t.ID,
       CASE WHEN SUM(CASE WHEN t.status = 2 THEN 1 ELSE 0 END) > 0
            THEN "Abnormal"
            ELSE "Normal" END AS Status,
       SUM(CASE WHEN t.status = 2 THEN 1 ELSE 0 END) AS AbnormalCount
FROM Table t
GROUP BY t.ID
Comments