CODEWITHSUNDEEP
sqlimpala
NastyDiaper
NastyDiaper

Reputation: 2558

Finding a total count in Impala SQL statement

I am trying to find a total count of all the users who have viewed a particular object in my particular table. I have a query which, so far, breaks it apart by object...

select object, count(distinct user) 'c' from events e where (action = 'viewed') and object in ('car','truck') group by object, 'c'

The output looks like the following...

car 92

truck 20

What I would like is to simply output the number 112. Any help would be great.

Upvotes: 0

Views: 5455

Answers (2)

Hogan
Hogan

Reputation: 70523

Just make it simpler to get what you want:

select count(*) 
from events e 
where action = 'viewed' 
and object in ('car','truck')

if you want distinct users by object you can do this

select count(*) 
from events e 
where action = 'viewed' 
  and object in ('car','truck') 
  and ROW_NUMBER() OVER (PARTITION BY Object, User) = 1

Upvotes: 0

juergen d
juergen d

Reputation: 204756

Put a sum() around your current query

select sum(c)
from
(
    select object, count(distinct user) 'c' 
    from events e 
    where (action = 'viewed') and object in ('car','truck') 
    group by object, 'c'
) tmp

Upvotes: 1

Related Questions