'Get average of column separated by groups

So I have a query that produces a table like the below, without the avg column. I'm trying to figure out how to add the avg column. The average should be from the profit of all stores within that month. I'm assuming I need to use AVG with a group by clause but can't seem to get it right.

I thought I could use a subquery to just get the rows for each month and average that, however I don't always know the number of months that will be in the table.

| store_id | month   | profit | avg|
------------------------------------
| 01       | Jan     | 100    | 200|
| 02       | Jan     | 200    | 200|
| 03       | Jan     | 300    | 200|
| 01       | Feb     | 250    | 250|
| 02       | Feb     | 200    | 250|
| 03       | Feb     | 300    | 250|
| 01       | Mar     | 150    | 100|
| 02       | Mar     | 50     | 100|
| 03       | Mar     | 100    | 100|
mysql


Solution 1:[1]

Create a temporary table to hold averages

CREATE TEMPORARY TABLE IF NOT EXISTS average
    SELECT month, avg(profit) AS `average profit` FROM store
        GROUP BY month;

Then join (left, inner) based on month

SELECT store.*, average.`average profit` FROM store
    LEFT JOIN average USING (month);

Fiddle link: https://www.db-fiddle.com/f/8u6DKWA8BmQgDmZxePLubn/0

Output:

| store_id | month   | profit | average profit |
| 01       | Jan     | 100    | 200            |
| 02       | Jan     | 200    | 200            |
| 03       | Jan     | 300    | 200            |
| 01       | Feb     | 250    | 250            |
| 02       | Feb     | 200    | 250            |
| 03       | Feb     | 300    | 250            |
| 01       | Mar     | 150    | 100            |
| 02       | Mar     | 50     | 100            |
| 03       | Mar     | 100    | 100            |

Solution 2:[2]

You can do that using this query. Select statement inside a Select statement.

SELECT *, (SELECT SUM(profit)/COUNT(*) FROM tbl1 
WHERE t.`month`=tbl1.`month`) AS ave FROM tbl1 t;

OR

SELECT *, (SELECT AVG(profit) FROM tbl1 
WHERE t.`month`=tbl1.`month`) AS ave FROM tbl1 t;

Result

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Danyal Imran
Solution 2

Related Questions