CODEWITHSUNDEEP
mysqlsqlaverage
Frederik
Frederik

Reputation: 101

Show the average value of a group in MySQL

My MySQL table contains a field that stores the user’s weight and a date field. I am trying to make a query that shows the average weight for a week.

My query so far looks like this:

SELECT Table.`id`,
       Table.`weight`,
       Table.`date`
FROM Table
WHERE id=%CURRENT_USER_ID%
ORDER BY date ASC
GROUP BY week(date);

This query generates a list where the results are grouped by week. But I also need the average weight for each week. Right now it only shows the weight for one entry.

How can I make this query?

Upvotes: 0

Views: 997

Answers (2)

Jon Armstrong
Jon Armstrong

Reputation: 4694

Be careful. Your SQL is not valid, even though MySQL can be configured to allow it.

This is an issue of functional dependence. The SELECT list terms must be functionally dependent on the GROUP BY terms.

You've selected id, which is not functionally dependent on week(date). In other words, your schema/logic does not guarantee that there is at most one id value per week(date) group. The same is true of trying to select date, which is also not guaranteed to resolve to just one value per week.

Your WHERE clause is also a problem. % can be used as a pattern in a LIKE expression, but not with the = operator, and was not properly quoted as a literal. I'll leave 'CURRENT_USER_ID' to represent your value, assuming that's the correct type. The table definition wasn't shown in the question.

A corrected version of your original query is:

SELECT week(`date`)  AS the_week
     , MIN(`weight`) AS min_weight
     , MIN(`id`)     AS min_id
  FROM Table
 WHERE id='CURRENT_USER_ID'
 GROUP BY week(date)
 ORDER BY the_week ASC
;

Note: The above uses the aggregate function MIN in the SELECT list. These expressions are functionally dependent on the GROUP BY terms. The AVG function can also be used, like this:

SELECT week(`date`)  AS the_week
     , AVG(`weight`) AS avg_weight
  FROM Table
 WHERE id='CURRENT_USER_ID'
 GROUP BY week(date)
 ORDER BY the_week ASC
;

and in MySQL, we can use the derived column name / alias in the GROUP BY terms.

SELECT week(`date`)  AS the_week
     , AVG(`weight`) AS avg_weight
  FROM Table
 WHERE id='CURRENT_USER_ID'
 GROUP BY the_week
 ORDER BY the_week ASC
;

That's a start.

Upvotes: 1

Muhammad Mujtaba Shafique
Muhammad Mujtaba Shafique

Reputation: 295

You can make a little changing and get your desired output. use this

 SELECT Table.`id`,
       Table.`weight`,
       Table.`date`,
       Avg('Table.weight')'Average Weight'
FROM Table
WHERE id=%CURRENT_USER_ID%
ORDER BY date ASC
GROUP BY week(date);

Upvotes: 0

Related Questions