find the max count in a subgroup

Question

I have a table (simplified)

I need to produce a report like this:

I need to find per user: total servers, total events, total succeeded and failed events. All this per user so I will be grouping by user.

But I'm also asked to compute the max number of events per single server per customer, i.e. subgrouping by server within each user. How do I do that?

Answer 1

You can do this with two levels of aggregation: first by user and server, then by user only.

select user_id, 
    count(*)          total_servers,
    sum(total_events) total_events,
    sum(succeeded)    succeeded,
    sum(failed)       failed,        
    max(total_events) max_per_server
from (
    select userid, server, 
        count(*) total_events,
        sum(case when event_status = 'success' then 1 else 0 end) succeeded,
        sum(case when event_status = 'success' then 1 else 0 end) failed
    from mytable t
    group by userid, server
) t
group by user_id

This should scan the table only once, and hence is likely to perform more efficiently than solutions involving joining CTEs.