Given the following table and records:
| location | interaction | session |
|---|---|---|
| us | 5 | xyz |
| us | 10 | xyz |
| us | 20 | xyz |
| us | 5 | qrs |
| us | 10 | qrs |
| us | 20 | qrs |
| de | 5 | abc |
| de | 10 | abc |
| de | 20 | abc |
| fr | 5 | mno |
| fr | 10 | mno |
I'm trying to create a query that will get a count of locations for all sessions that have interactions of 5 and 10, but NOT 20.
So assuming the above, the query will return
| count | location |
|---|---|
| 2 | us |
| 1 | de |
FR will not be in the results, because session 'mno' did not have an interaction of 20. As far as I can tell, I need to group by session first, then group by location afterwards. Which might mean using a nested select statement. I've tried a few things, but am not sure how to proceed. Would appreciate any help on how to approach a query like this.
CodePudding user response：
Maybe your example is wrong - if a location session shall have interactions 5 and 10 but not 20 then only 'fr' shall be in the result as both 'us' and 'de' do have 20 in all their sessions.
SQL fiddle here.
with t as
(
select location, array_agg(interaction) arr
from the_table
group by location, session
)
select count(*) cnt, location
from t
where arr @> array[5,10] and not arr @> array[20]
group by location;
CodePudding user response：
For Oracle SQL try this:
select count(location), location from TABLENAME where interaction in (5,10) group by location;
Change TABLENAME to your table.