Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
May 2nd, 2013, 11:25 PM
 Billiam80
Registered User

Join Date: May 2013
Posts: 2
Time spent in forums: 28 m 49 sec
Reputation 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
May 3rd, 2013, 12:35 AM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,824
Time spent in forums: 3 Months 1 Week 4 Days 9 h 14 m 36 sec
Reputation Power: 4208
Quote:
 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'
__________________
r937.com | rudy.ca

#3
May 3rd, 2013, 12:52 AM
 Billiam80
Registered User

Join Date: May 2013
Posts: 2
Time spent in forums: 28 m 49 sec
Reputation Power: 0
Awesome! That worked perfectly. Thank you for such a quick response

 Viewing: Dev Shed Forums > Databases > MySQL Help > Average value before a DATE if IN(X,Y,Z) on that DATE