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

@Steve Hi! Here's a quick point; it would be easier to understand your situation if you formatted your sample data into tables. — Ben, Jan 10 '18 at 13:02
Further to other comments, please also tag the RDBMS you're using, and include the full query you're currently running, along with expected results. Thanks https://stackoverflow.com/help/mcve — Gareth Lyons, Jan 10 '18 at 13:04
See the help on formatting to understand how to show tables as text: http://stackoverflow.com/help/formatting I also use http://plaintexttools.github.io/plain-text-table/ to prepare ASCII tables before posting — , Jan 10 '18 at 13:08

score 0 · Answer 1 · answered Jan 10 '18 at 13:09

0

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

answered Jan 10 '18 at 13:09

Cristian Lupascu

39,078
16
100
137

1

Thanks for the reply. I get the error; Invalid operation: WINDOW definition is not supported; Using SQL workbench connecting to Amazon Redshift – Steve Jan 10 '18 at 13:15
Is there an alternative method to partititon? Seems its not supported – Steve Jan 10 '18 at 13:30
This don't use window function, but if some similar alternative exists in Amazon Redshift, you should use that. There I also assume that month is part of join key. `select t1.Month, t1.Event, t2.Location, (select count(1) from Table1 st1 where st1.Event = t1.Event and st1.month = t1.month ) as EventCount, count (*) LocationCount from Table1 t1 left join Table2 t2 on t1.Id = t2.Id and t1.month = t2.month group by t1.Month, t1.Event, t2.Location;` – Edgars T. Jan 10 '18 at 13:45
1

@RadimBača what further information can I provide? I am using Amazon Redshift and SQL Workbench. When I run the code suggested I get the error WINDOW definition is not supported. You say there is an alternative to Window function in Redshift. Could you point me in the right direction? Thanks – Steve Jan 10 '18 at 13:59

SQL Query - Two Counts Different Granularity

1 Answers1