'Hive/ Impala query group by query for total success and failed record

I am trying to add the group by clause on the impala/Hive table but its not working.

I am having the jobs details table which having job name and status column.

Table jobs_details :
---------------------
Job name       status
---------------------
A              failed
B              Failed
A              success
A              failed
----------------------------------
I want the below type output :
----------------------------------
Job name           failed_count success_count
 A                      2               1
 B                      1               0

I tried to use the group by clause on job name but it's showing me total count ( failed + success )

sqlpostgresqlhiveimpalahadoop-streaming


Solution 1:[1]

Below query might help you in getting the desired result.


SELECT job,
Sum(CASE WHEN status = 'Success' THEN 1 ELSE 0 END) AS Success, 
Sum(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) AS Failed
FROM   temp
GROUP  BY job

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Srishuk Kumar Bhagwat

Related Questions