generate a mean for a 2-uple with MySQL

I can generate a table from records like that :

ID|Var1|Var2|Measure
1   10   13    10
1   10   15    8
1   15   13    0
    ...

One ID can have several Var2 that are identic开发者_开发问答al. How I can generate a mean for each 2-uple ID-Var2 like that :

ID|Var2|Mean_Measure
1   13    5
1   14    8
...
2   13    7

Thank you

You would need to use a GROUP BY clause to group the rows with the same ID and Var2 together and then the AVG function calculates the average:

SELECT t.ID, t.Var2, AVG(t.Measure) AS Mean_Measure FROM YourTable t GROUP BY t.ID, t.Var2

I might add that GROUP BY will alter the output of the query quite a bit. It also adds some restrictions on the output. First off - after a group by you can only add expressions in the SELECT clause where one the following applies:

1. The expression is part of the GROUP BY clause
2. The expression is an application of an aggregate function

In the above example t.ID and t.Var2 exists in the GROUP BY clause and AVG(t.Measure) is an application of the aggregate function AVG on t.Measure.

When dealing with WHERE clauses and GROUP BY there's also some things to note:

1. WHERE is applied after the GROUP BY this means generally that expressions not in GROUP BY cannot be used in the WHERE clause
2. If you wish to filter data before the GROUP BY use HAVING instead of WHERE

I hope this makes sense - and for more and better information on how GROUP BYs work - I'd suggest consulting the MySQL manual on the topic.