how to write case and group by in hive query
<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>