CODEWITHSUNDEEP
mysqlsqldatabase
user3241112
user3241112

Reputation: 69

MAX, SUM then AVG all in one SQL query?

Complicated SQL query needed here, not sure if it's even possible.

I have a table like this:

------------------------
|uid |lesson_id |score |
------------------------
|1   |0         |20    |
|1   |0         |25    |
|1   |0         |15    |
|1   |0         |40    |
|1   |1         |70    |
|1   |0         |10    |
|1   |1         |20    |
|1   |1         |55    |
|1   |1         |55    |
|1   |0         |5     |
|1   |2         |65    |
------------------------

For a particular user, I need to get the maximum score for each lesson, sum them, then get their average... all in a single SQL query.

Max scores:

-----------------------
|lesson_id |max_score |
-----------------------
|0         |40        |
|1         |70        |
|2         |65        |
-----------------------

Sum:

------
|sum |
------
|175 |
------

Average:

-------
|avg  |
-------
|58.3 |
-------

How's this?

SELECT uid AS id, AVG(max_score) AS avg_score FROM 
    (
    SELECT uid, lesson_id, max(score) AS max_score FROM cdu_user_progress 
    WHERE session_id = $session_id
    GROUP BY uid, lesson_id 
) AS tt 
WHERE uid IN ($ids) 
GROUP BY uid 
ORDER BY avg_score

Upvotes: 2

Views: 2755

Answers (5)

DougM
DougM

Reputation: 2888

You can easily do this in a single SQL query, although you will need use more than one statement.

Most easily, you can do this via a subquery, although the same result can be done via a temporary table or Common-Table-Expression.

(Also, note that the SQL AVG function already sums and divides results by their count. You don't want to do an AVG(SUM(X)), as it'll wind up with a nonsense figure.) Anyway, on to the example:

First, write a SQL expression to get the items you will be averaging, such as the max score for each test for each user.

SELECT MAX(Score) As MaxScore, uid, lesson_id
FROM table
GROUP BY uid, lesson_id

Then, you treat the above SELECT as a table, and do a second select around it to get the average Max score per uid.

SELECT AVG(MaxScore) as Final, uid 
FROM (
    SELECT MAX(Score) As MaxScore, uid, lesson_id
    FROM table
    GROUP BY uid, lesson_id
) AS subQuery
GROUP BY uid

You can easily include additional aggregation functions using this method, and as it's fairly standard it will work in most SQL servers. However, depending on vendor you may have custom-built syntax to do this, such as OVER and Parition by in SQL Server

Upvotes: 0

D Stanley
D Stanley

Reputation: 152644

You can just use subqueries:

SELECT uid, SUM(MaxScore), Avg(MaxScore) 
FROM
(
  SELECT uid, lesson_id,max(score) as MaxScore
  FROM scores 
  GROUP BY userid, lesson_id 

) AS m
GROUP BY  uid

or for just one user:

SELECT SUM(MaxScore), Avg(MaxScore) 
FROM
(
  SELECT lesson_id,max(score) as MaxScore
  FROM scores 
  WHERE uid = $uid
  GROUP BY lesson_id 
) AS m

or for "a bunch of specific users":

$uidlist = (comma-delimited list of user ids)

SELECT uid, SUM(MaxScore), Avg(MaxScore) 
FROM
(
  SELECT uid, lesson_id,max(score) AS MaxScore
  FROM scores 
  WHERE FIND_IN_SET(uid, $uidlist)
  GROUP BY uid, lesson_id 
) AS m
GROUP BY uid

Upvotes: 2

Mutale
Mutale

Reputation: 310

SELECT AVG( s.scores ) AS avarage, SUM( s.scores ) AS total
FROM (

SELECT lesson_id, MAX( score ) AS scores
FROM  `tblScores` 
GROUP BY lesson_id
) AS s

Upvotes: 0

MrSimpleMind
MrSimpleMind

Reputation: 8647

Here are a simple sample SQLFIDDLE

select Avg(tt.maxscores) from (
select lesson_id,max(score) as maxscores
from t
group by lesson_id ) as tt

Upvotes: 1

useSticks
useSticks

Reputation: 904

Everything is possible, it just may be very very ugly. Here is an example of ugly code that can get what you want in a single query...

PSEUDO Code Query:

SELECT lesson_id, max_score WHERE uid=X
UNION
SELECT -1, sum //done via nested query
UNION
SELECT -2, average //done via nested query

Upvotes: -1

Related Questions