Danny David Leybzon Danny David Leybzon - 4 months ago 8
SQL Question

How do I count the number of commands a user ran?

I have a table that looks like this

user_id ...
1 ...
1 ...
2 ...


And I would like a table that looks like this

user_id num_commands ...
1 2 ...
2 1 ...


I'm using the query

select
user_id as id,
count(user_id) as num_commands,
...
from table_name
group by user_id


But it's returning the error
FAILED: SemanticException [Error 10025]: Line 4:0 Expression not in GROUP BY key 'num_commands'
. This seems nonsensical to me; I realize that num_commands isn't in the GROUP BY statement, but it's the result of an aggregating function, so why does it matter?

Answer

this is wrong

select
 user_id as id,
 count(user_id) as num_commands
 from table_name
 group by user_id

count(user_id) and group by user_id raise the Exception

select
 user_id as id,
 count(user_id) as num_commands,
 other_column1, 
 other_column2 
 from table_name
 group by user_id

this is wrong because not the column in group by

this is right

select
 user_id as id,
 count(*) as num_commands,
 other_column1, 
 other_column2 
 from table_name
 group by user_id, other_column1,      other_column2 


select
 user_id as id,
 count(*) as num_commands,
 sum(other_column1), 
 avg(other_column2) 
 from table_name
 group by user_id
Comments