hive - Transpose and add new record in SQL

Question

SELECT COUNT(DISTINCT CASE WHEN customer_name = 'Alex' THEN item END) AS by_Alex,   
       COUNT(DISTINCT CASE WHEN customer_name = 'Bob' THEN item END) AS by_Bob, 
       COUNT(DISTINCT CASE WHEN customer_name = 'Jim' THEN item END) AS by_Jim
FROM sales;

Outputs this result

How can I arrange this code to transpose as a matrix (w/2 columns "Name" and "Count") and add a new row called "by_Christian" by summing up "by_Bob" (2) and "by_Alex" (3) and add 5 under "Count" column?

Answer 1

I think you simply want aggregation:

select name, count(*) as num_items, count(distinct item) as num_distinct_items
from sales
group by name;