Skip to content
Advertisement

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.

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:

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:

Any ideas? Thanks in advance

Advertisement

Answer

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

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement