SQLTeam.com | Weblogs | Forums

SQL Query - Two Counts Different Granularity


#1

have two tables, when joined it produces the following;
image
Now, I want to create a Query which counts all events, and their corresponding Location. Given the above example, it should look like this;


#2

image

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


#3

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
;