SQL query with avg and group by

Question

I have some problems with writing a SQL query for MySQL  I have a table with the following structure   mysql gt  select id  pass  val from data r1 limit 10   ------------ -------------- ----------------    id           pass           val               ------------ -------------- ----------------    DA02959106   5 0000000000    44 4007000000     08A5969201   1 0000000000   182 4100000000     08A5969201   2 0000000000   138 7880000000     DA02882103   5 0000000000    44 7265000000     DA02959106   1 0000000000   186 1470000000     DA02959106   2 0000000000   148 2660000000     DA02959106   3 0000000000   111 9050000000     DA02959106   4 0000000000    76 1485000000     DA02959106   5 0000000000    44 4007000000     DA02959106   4 0000000000    76 6485000000     I want to create a query that extracts the following information from the table   id  AVG of  val  for  pass    1  AVG of  val  for  pass    2  etc   The result of the query should look like this    ------------ --------- --------- --------- --------- --------- --------- ---------    id           val 1     val 2     val 3     val 4     val 5     val 6     val 7      ------------ --------- --------- --------- --------- --------- --------- ---------    DA02959106   186 147   148 266   111 905   76 3985   44 4007   0         0          ------------ --------- --------- --------- --------- --------- --------- ---------    with more rows for each unique  id   of course    I already tried some queries like  SELECT id  pass  AVG val  AS val 1 FROM data r1 WHERE pass   1 GROUP BY id    This returns the correct result  but I have to expand it with results for the other possible values of  pass   up to 7   I tried to use a nested SELECT within AVG but this didn t work because I didn t figure out how to correctly limit it to the current  id    I then created Views to represent the result of each query for  pass    1   pass    2  etc  But for most ids the highest value for  pass  is 5  When using JOIN queries to get the final result from the views I received an empty result set  because some of the Views are empty   don t have values for a specific  id    Any ideas

User · Accepted Answer

If I understand what you need  try this   SELECT id  pass  AVG val  AS val 1  FROM data r1  GROUP BY id  pass    Or  if you want just one row for every id  this   SELECT d1 id       SELECT IFNULL ROUND AVG d2 val   4   0  FROM data r1 d2       WHERE d2 id   d1 id AND pass   1  as val 1       SELECT IFNULL ROUND AVG d2 val   4   0  FROM data r1 d2       WHERE d2 id   d1 id AND pass   2  as val 2       SELECT IFNULL ROUND AVG d2 val   4   0  FROM data r1 d2       WHERE d2 id   d1 id AND pass   3  as val 3       SELECT IFNULL ROUND AVG d2 val   4   0  FROM data r1 d2       WHERE d2 id   d1 id AND pass   4  as val 4       SELECT IFNULL ROUND AVG d2 val   4   0  FROM data r1 d2       WHERE d2 id   d1 id AND pass   5  as val 5       SELECT IFNULL ROUND AVG d2 val   4   0  FROM data r1 d2       WHERE d2 id   d1 id AND pass   6  as val 6       SELECT IFNULL ROUND AVG d2 val   4   0  FROM data r1 d2       WHERE d2 id   d1 id AND pass   7  as val 7 from data r1 d1 GROUP BY d1 id

User · Answer

As I understand  you want the average value for each id at each pass  The solution is  SELECT id  pass  avg value  FROM data r1 GROUP BY id  pass

[mysql] SQL query with avg and group by

Examples related to mysql

Examples related to sql

Examples related to group-by

Examples related to average