Help for a complex Mysql query

[subuntug]

Hello every body, I have a table which contains exams' marks for student in different modules. Each student can have one or many marks for each module in the table. When I have more than one mark for a student in a module, I have to keep the highest mark. I need to calculate the average for each student in my table. How can I do this with mysql query. I know that there is a query like:

SELECT AVG(mark) FROM MyTable WHERE student = 'MyStudent';

But, like I said before, each student may have one or more marks in each module, and in this case, I need to keep the best one for the module where there is more than 1 mark. I tried this query:

SELECT AVG(mark) FROM (SELECT MAX(mark) FROM MyTable WHERE student='MyStudent';

But it doesn't seem to work. I got the error message : "#1248 - Every derived table must have its own alias" Thanks in advance for your help.

[thescientist]

Hello every body, I have a table which contains exams' marks for student in different modules. Each student can have one or many marks for each module in the table. When I have more than one mark for a student in a module, I have to keep the highest mark. I need to calculate the average for each student in my table. How can I do this with mysql query. I know that there is a query like:

SELECT AVG(mark) FROM MyTable WHERE student = 'MyStudent';

But, like I said before, each student may have one or more marks in each module, and in this case, I need to keep the best one for the module where there is more than 1 mark. I tried this query:

SELECT AVG(mark) FROM (SELECT MAX(mark) FROM MyTable WHERE student='MyStudent';

But it doesn't seem to work. I got the error message : "#1248 - Every derived table must have its own alias" Thanks in advance for your help.

I'm not sure which one you need, since you are asking for the highest and the average from your query but this would give you go both (with a little work).

SELECT marks FROM MyTable WHERE student = 'MyStudent' ORDER BY DESC

that way the highest grade would be on 'top', and if you needed the average, it's just a matter of adding up all the returned rows and dividing by the count().

[subuntug]

I'm not sure which one you need, since you are asking for the highest and the average from your query but this would give you go both (with a little work).

SELECT marks FROM MyTable WHERE student = 'MyStudent' ORDER BY DESC

that way the highest grade would be on 'top', and if you needed the average, it's just a matter of adding up all the returned rows and dividing by the count().

Thanks for your answer.But that's not what I need. I'm trying to keep the highest mark for each module not for the entire modules. My table looks like this: +---------+---------+---------------+-------+ | student|module |mark Number |mark | +---------+---------+---------------+-------+ | Mike |Module1 | 1 |15 | +---------+---------+---------------+-------+ |Mike |Module1| 2 |11 +---------+---------+---------------+-------+ | Mike |Module2 | 1 |9 +---------+---------+---------------+-------+ |Mike |Module3 | 1 |10 +---------+---------+---------------+-------+ My question would be, how to select the marks for "Mike" for all the modules, but when a module has more than one mark, ( in my exemple above, the module1 has 2 marks the same student) select only the best mark. I hope my question is now more clear. Thanks in advance for your help.

[Fmdpa]

SELECT AVG(mark) FROM (SELECT MAX(mark) FROM MyTable WHERE student='MyStudent';

I've never heard of doing embedded queries, so I don't know if this is supposed to work. But the problem I see first with this query is that there is an unclosed parenthesis. You might also try using aliases.

SELECT AVG(mark) AS avg_mark FROM (SELECT MAX(mark) FROM MyTable WHERE student='MyStudent');

[birbal]

using group by will work i think...

I'm trying to keep the highest mark for each module not for the entiremodules.

SELECT MAX(mark) FROM MyTable WHERE student = 'MyStudent' GROUB BY module;

My question would be, how to select the marks for "Mike" for all the modules, but when a module has more than one mark, ( in my exemple above, the module1 has 2 marks the same student) select only the best mark.

SELECT MAX(mark) FROM MyTable WHERE student = 'MyStudent' GROUB BY student;

[subuntug]

using group by will work i think...

SELECT MAX(mark) FROM MyTable WHERE student = 'MyStudent' GROUB BY module;

SELECT MAX(mark) FROM MyTable WHERE student = 'MyStudent' GROUB BY student;

Thank you for your answer, but that's not the solution. Because this query selects just one mark, it selects the best mark for the student amoung all the modules, not the best mark for each module. If there's 10 modules and 15 marks, it should select 10 marks only. Each time there's more thank one mark for a module, it should select the best mark. That's what I need.