CODEWITHSUNDEEP
mysql
Gabriel Moretti
Gabriel Moretti

Reputation: 676

a query that returns a single row for each foreign key

I have a table of routines. In this table, I have the column "grade" (which is not mandatory), and the column "date". Also, I have a number of days and an array of ids of users. I need a query that returns me the last routine that have a value != null for "grade" column and datediff(current_date,date) >= number_of_days for each id in the array and make an average of all these values.

e.g.

today = 2014/10/15

number_of_days = 10

ids(1,3)

routines

id  | type | date       | grade | user_id
1   | 1    | 2014-10-10 | 3     | 1
2   | 1    | 2014-10-04 | 3     | 1
3   | 1    | 2014-10-01 | 3     | 1
4   | 1    | 2014-09-24 | 2     | 1
5   | 1    | 2014-10-10 | 2     | 2
6   | 1    | 2014-10-04 | 3     | 2
7   | 1    | 2014-10-01 | 3     | 2
8   | 1    | 2014-09-24 | 1     | 2
9   | 1    | 2014-10-10 | 1     | 3
10  | 1    | 2014-10-04 | 1     | 3
11  | 1    | 2014-10-01 | 1     | 3
12  | 1    | 2014-09-24 | 1     | 3

In this case, my query would return an avg between "grade" of row id #2 and #10

Upvotes: 0

Views: 296

Answers (1)

John Bollinger
John Bollinger

Reputation: 180141

I think you're saying that you want to consider rows having non-null values in the grade column, a date within a given number of days of the current date, and one of a given set of user_ids. Among those rows, for each user_id you want to choose the row with the latest date, and compute an average of the grade columns for those rows.

I will assume that you cannot have any two rows with the same user_id and date, both with non-null grades, else the question you want to ask does not have a well-defined answer.

A query along these lines should do the trick:

SELECT AVG(r.grade) AS average_grade
  FROM 
    (SELECT user_id, MAX(date) AS date
      FROM routines
      WHERE grade IS NOT NULL
        AND DATEDIFF(CURDATE(), date) >= 10
        AND user_id IN (1,3)
      GROUP BY user_id) AS md
    JOIN routines r
      ON r.user_id = md.user_id AND r.date = md.date

Note that in principle you need a grade IS NOT NULL condition on both the inner and the outer query to select the correct rows to average, but in practice AVG() ignores nulls, so you don't actually have to filter out the extra rows in the outer query.

Upvotes: 1

Related Questions