title: how to write case and group by in hive query
body: <p>This is my hive table:</p>
  
  <pre><code>course   dept    subject   status
  
  btech     cse     java     pass
  btech     cse     hadoop   fail
  btech     cse     cg       detained
  btech     cse     cc       pass
  btech      it     daa      pass
  btech      it     wt       pass
  btech      it     cnn      pass
  mba        hr     hrlaw    pass
  mba        hr     hrguid   absent
  mtech      cs     java     pass
  mtech      cs     cd       pass
  mtech      cs     cp       detained
  </code></pre>
  
  <p>I want to query this table to retrieve data in the following way:</p>
  
  <pre><code>course   dept    status
  
  btech     cse     fail
  btech      it     pass
  mba        hr     absent
  mtech      cs     fail
  </code></pre>
  
  <p>First, it will check for "fail" or "detained" in the <code>status</code> of each <code>dept</code> and <code>course</code> grouped together. If it finds "fail" or "detained", it will output "fail" as the <code>status</code>. Else, if an "absent" is found in the same group, it will output "absent" as the <code>status</code>. Else, it will output "pass".</p>
  
  <p>I got an error message when I ran the following query:</p>
  
  <pre><code>select course,dept,
  case 
  when status in ( 'fail','detained') then 'fail'
  when status in ( 'absent') then 'absent'
  when status in ( 'pass') then 'pass'
  else null 
  end as Final_Status
  from college
  group by course,dept;
  </code></pre>