CODEWITHSUNDEEP
sqlmysql
web21
web21

Reputation: 1

SQL Query -AVG Last two days

I m trying to get Average of last two records and group them by name. 

OId     ODate        Value      Name
1      05/01/2011   25          Mike
2      05/01/2011   56      Peter
3      05/01/2011   09      Robert
4      06/01/2011   21          Mike
5      06/01/2011   52      Peter
6      06/01/2011   04      Robert
7      10/01/2011   28          Mike
8      10/01/2011   57      Peter
9      10/01/2011   03      Robert

SELECT Name, avg(Value) as avg_score
FROM (
  SELECT Name, Value FROM S_ORDER BY dessc ODate 60
)
GROUP BY Name;

Thank you.!

Upvotes: 0

Views: 458

Answers (2)

user330315
user330315

Reputation:

This would be a standard ANSI SQL solution:

SELECT Name, avg(Value) as avg_score
FROM (
  SELECT Name, 
         Value, 
         row_number() over (partition by name order by Odate desc) as row_num
  FROM your_table
) t
GROUP BY Name
WHERE row_num <= 2;

Upvotes: 1

The Scrum Meister
The Scrum Meister

Reputation: 30111

SELECT tLast.Name, 
  (tLast.Value + COALESCE(tLast2.Value, 0)) / CASE WHEN tLast2.Value IS NULL 1 ELSE 2 END As avg_score
FROM (
    SELECT last1.Name, MAX(last1.MaxID) MaxID, MAX(last2.OId) MaxID2
    FROM (
        SELECT Name, MAX(OId) MaxID
        FROM tableName
        GROUP BY Name
      ) last1 LEFT JOIN tableName last2 ON Name = last1.Name AND OId < last1.MaxID
    GROUP BY last1.Name
  ) rs JOIN tableName tLast ON rs.MaxID = tLast.OId
  LEFT JOIN tableName tLast2 ON rs.MaxID2 = tLast2.OId
ORDER BY tLast.Name

Grouping by name is not recommended, you should store a user_id which points to a user table, instead of storing the name.

Upvotes: 1

Related Questions