SQL Query - Two Counts Different Granularity

Question

so I have two tables;

When Joined together (OUTER JOIN on ID) it looks like this

Now, I want to create a Query which counts all events, and their corresponding Location. Given the above example, it should look like this;

The key thing is that the COUNT (Event) needs to be aggregated at a higher level than COUNT(Location). i.e. the Count (Event) is a TOTAL count for this event for the entire month. The Count (Location) is simply that Event count, split into their relevant locations.

I cant get my query to aggregate the Count Event at a higher level than the Locations.

Happy to provide more detail

Thanks

Answer 1

You can use window functions to achieve this:

select
  t1.Month,
  t1.Event,
  t2.Location,
  count (*)
    over (partition by t1.Event) as EventCount,
  count (*)
    over (partition by t1.Event, t2.Location) as LocationCount
from Table1 t1
left join Table2 t2
  on t1.Id = t2.Id

SQLFiddle: http://sqlfiddle.com/#!6/b55b3/8