Average over value for distinct ids with many to many relationship-CodePudding

I have these two tables:

Events

ID	Value1	Value2
0	0	1
1	1	2
2	0	3
3	1	1

Many to many

EventID	Values3
0	1
0	5
0	6
1	2
2	0
2	7
3	7

and two lists:

Values2List = [1,3]
Values3List = [0,2,3,4,7,8]

I need to get the Average value of Value1 for all Events where Value2 is in Values2List and ANY of the corresponding Values3 are in the Values3List. However I do NOT want to double count Events that have multiple matching Value3's

I am thinking something like:

SELECT AVG(e.Value1)
FROM Events e
JOIN ManyToMany m
ON e.ID = m.EventID
WHERE e.Value2 in Values2List
AND m.Values3 in Values3List

http://sqlfiddle.com/#!9/4fde479/2/0

This would give me the average of all values that pass but it would double count those that have multiple values3's in the list and gives me 1/3 instead of the desired 1/2.

How to fix this?

CodePudding user response：

First get the distinct combinations of ID and value1 from the join and then aggregate:

SELECT AVG(value1)
FROM (
  SELECT DISTINCT e.ID, e.value1
  FROM events e JOIN MtoM m
  ON e.ID = m.EventID
  WHERE e.value2 IN (1,3) AND m.value3 IN (0,2,3,4,7,8)
) t

See the demo.

CodePudding user response：

Might do something like this

select avg(e.Value1)
  from events e
 where e.value2 in (1,3)
   and exists
         ( select 1
             from many_to_many mm
            where e.id = mm.event_id
              and mm.value3 in (0,2,3,4,7,8)
         )

CodePudding user response：

You can filter the second table and put the result in the join

SELECT AVG(e.value1)
FROM Events e
INNER JOIN 
(select distinct t.EventID from MtoM t where t.value3 in (0,2,3,4,7,8))m
ON e.ID = m.EventID
WHERE e.value2 in (1,3)