CODEWITHSUNDEEP
sqlselectdistinctrows
muh'd bashir
muh'd bashir

Reputation: 13

distinct in sql

This is my SQL:

SELECT DISTINCT(CASE WHEN state=18 THEN lga ELSE 'Others' END) LGA,
    COUNT(CASE WHEN choice=21 THEN choice END ) NDCH
FROM bio   
GROUP BY lga

My expectation is for it to give me all LGA with state=18, and if the state is not equal to 18 let them be grouped as 'Others', but I usually have more than one 'Others'. Please help.

Upvotes: 1

Views: 1986

Answers (1)

Andomar
Andomar

Reputation: 238296

Consider a group by on the case statement:

SELECT  CASE WHEN state=18 THEN lga ELSE 'Others' END LGA
,       COUNT(CASE WHEN choice=21 THEN choice END) NDCH
FROM    bio
GROUP BY
        CASE WHEN state=18 THEN lga ELSE 'Others' END

Upvotes: 2

Related Questions