have two tables, when joined it produces the following;
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
As you didn't post the query you have so far, this query assumes to have the result in table_result:
Query
select distinct
[month]
,[event]
,[location]
,count([event]) over(partition by [month],[event]) as [event]
,count(isnull([location],'sa/hw')) over(partition by [month],[event],[location]) as [location]
from table_result
;