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

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0

    Post 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. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. 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

IMN logo majestic logo threadwatch logo seochat tools logo