how can I count a record that has a one to many relationship, based on importance of an attribute

I am attempting to get counts of actions grouped by the grouping and source fields. The issue is, there is some overlap between the actions and source. So I would like one source to take priority over the other if an x.id is found in both.

select x.grouping, o.source, o.event, count(o.event) event_count
from xtable x 
join etable e on x.id = e.id
join otable o on x.xid = o.oid
where e.pass = '1'
and o.source in ('205','206')
group by grouping, o.event, o.source;

id and xid, oid are just identifying id’s on the tables that will allow us to join.

The result of this looks something like:

group|  source | event  | event_count
QU1  |  205    |  A     | 75
QU1  |  205    |  B     | 7
QU1  |  205    |  C     | 34
QU1  |  206    |  A     | 103
QU1  |  206    |  B     | 65
QU1  |  206    |  C     | 12

The issue I have is that the total number of records in that group is 195 records, so some of them are getting counted more than once.

My question is, how can I get the record to get counted once, based on the source? For example, source 205 is more accurate than 206, so i’d rather those events be counted at the 205 event, and not included in the 206 event count. But if the ID is not in the 205 event, then it should get counted. It is possible for the id to be in two different events at the different source, in which case it still should not be counted in the 206 if its found in the 205.

The updated result could look something like:

group|  source | event  | event_count
QU1  |  205    |  A     | 75
QU1  |  205    |  B     | 7
QU1  |  205    |  C     | 34
QU1  |  206    |  A     | 28
QU1  |  206    |  B     | 58
QU1  |  206    |  C     | 12

Any ideas? Thanks in advance

Answer

Hmmm . . . You can use row_number() for the prioritization — to choose only one event. Then aggregate:

select grouping, source, event, count(*) as event_count
from (select x.grouping, o.source, o.event, 
             row_number() over (partition by x.grouping, o.event order by o.source) as seqnum
      from xtable x join
           etable e
           on x.id = e.id join
           otable o 
           on x.xid = o.oid
      where e.pass = '1' and o.source in ('205', '206')
     ) xeo
where seqnum = 1
group by grouping, o.event, o.source;

Note: This assumes that 205 is prioritized first over 206.