### Thread: Average value before a DATE if IN(X,Y,Z) on that DATE

1. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
May 2013
Posts
2
Rep Power
0

#### Average value before a DATE if IN(X,Y,Z) on that DATE

Hello all,

Bear in mind, I am very new and trying to learn

I am trying to figure out how to select the average value from table1 for category A for all days before 2013-04-14 if the ID in table has a Pos IN(100,200,300) on 2013-04-14.
+ denotes values that I would want

---table1---
Date | ID | CAT | Value
-----------------------------
4-10 | 1 | A | 2 +
4-11 | 2 | A | 4 +
4-11 | 3 | B | 3
4-11 | 1 | A | 1 +
4-12 | 4 | B | 1
4-12 | 2 | A | 1 +
4-13 | 3 | B | 0
4-14 | 4 | B | 2

---table2---
Date | ID | CAT | Pos
----------------------------
4-10 | 1 | A | 100
4-11 | 2 | A | 200
4-12 | 3 | B | 999
4-13 | 4 | B | 999
4-14 | 1 | A | 300
4-14 | 3 | B | 999
4-14 | 2 | A | 100
4-14 | 4 | B | 200

So... I want my output to read

Cat | Value
------------
A | 2

Is there a way to get this? I've been researching for days and I'm at my wits end.
2. Originally Posted by Billiam80
... select the average value from table1 for category A for all days before 2013-04-14 if the ID in table has a Pos IN(100,200,300) on 2013-04-14.
Code:
```SELECT AVG(t1.value)
FROM table1 AS t1
INNER
JOIN table2 AS t2
ON t2.id = t1.id
AND t2.date = '2012-04-14'
AND t2.pos IN (100,200,300)
WHERE t1.cat = 'A'
AND t1.date < '2012-04-14'```
3. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
May 2013
Posts
2
Rep Power
0
Awesome! That worked perfectly. Thank you for such a quick response