Hive query to group

Question

I have the following table columns: ID (id), Type (string. could be either: open or close)

I would like to get an output count where for each unique ID, I count how many times the ID got the open type and how many times it got the close type.

So the output would look like this:

ID | openCount | closeCount
10 | 23        | 2

Any ideas?

Answer 1

select ID,count( * ) AS "openCount" where type = "open" group by ID union select ID,count( * ) AS "closeCount" where type = "close" group by ID;

that should do it. it's count(star) stack overflow doesn't seem to like that :)

Answer 2

SELECT id, SUM(type='open') openCount, SUM(type='closed') closedCount FROM table GROUP BY id;

What happens is that the SUM() function aggregates all type='open' and type='closed' values for all rows which share the same id value, which if true is 1 and if false is 0.