Error for the query statement with CASE and Count

This is my original query statement….

 SELECT airline company, country, count(country) "The no.of times the airline visits the country"
 FROM [table]
 GROUP BY airline company, country

I think this works fine…until I want to add a case statement where there is an error……

After modification,

 Airline Company      : country  : No.Of times
   Singapore Airline  : SG       :   5
   Singapore Airline  : MAL      :   7
   Japan Airline      : SG       :   3
   Japan Airline      : MAL      :   2

After the 1st query statement, the result is shown as above. I would like to change the value of No.of times column to 0 regardless of the airline for the country ‘MAL’. So I tried to add the case statement to the select, but it gives me like group function is not allowed

 SELECT airline company, country, CASE WHEN country = 'MAL' THEN 0 
                                       ELSE count(country) "The no.of times the airline visits the country" END
 FROM [table]
 GROUP BY airline company, country, CASE WHEN country = 'MAL' THEN 0 
                                       ELSE count(country) "The no.of times the airline visits the country" END

How do I fix it?

Answer

I would do Conditional Aggregation :

SELECT airline_company, country, 
       COUNT ( CASE WHEN country = 'MAL' THEN NULL 
                    ELSE country 
                    END ) as "# of times the airline visits"
  FROM [table]
 GROUP BY airline_company, country

Advertisement

Answer