Hive Query CASE WHEN Status

Question

I Want to write query using hive with CASE and WHEN.

SELECT CASE WHEN home_score > away_score THEN away_team 
            WHEN home_score < away_score THEN home_team  
        END AS year_group, 
      COUNT(1) AS count  
  FROM matches 
GROUP BY year_group 
ORDER BY count desc 
limit 5;

but this not working in hive. Please help me to solve this problem.

Answer 1

Add CASE to the GROUP BY:

SELECT CASE WHEN home_score > away_score THEN away_team 
            WHEN home_score < away_score THEN home_team  
        END AS year_group, 
      COUNT(1) AS cnt
  FROM matches 
GROUP BY CASE WHEN home_score > away_score THEN away_team 
              WHEN home_score < away_score THEN home_team  
         END
ORDER BY cnt desc 
limit 5;