How can I get the total count and the count with the condition in the same SQL query?

Question

I have a table like this:

id	value
0001	0
002	100

I want to make a table like this:

total_count	fail_count	pass_rate
2	1	0.5

May I know how to write in one Hive SQL query? Thanks.
the pass_rate is fail_count/total_count.

I tried to use WHERE LENGTH(id) = 3, but it returns 0 as count for me.

Here is my sql query:

SELECT 
    COUNT(*) AS total_count, 
    COUNT(CASE WHEN LENGTH(id) = 3 AND (value IS NULL OR value <= 0) THEN 1 ELSE NULL END) AS fail_count 
FROM my_table

And this is the output of my SQL query:

total_count	fail_count
2	0

Answer 1

Use case expressions + aggregations like this:

select count(*) as total_count, 
       count(case when LENGTH(id) = 3 then 1 else null end) as id_length_3,
       sum(value)/count(*)/100 as pass_rate
   from mytable