How to take average of two columns row by row in SQL?

Question

I have a table match which looks like this (please see attached image). I wanted to retrieve a dataset that had a column of average values for home_goal and away_goal using this code

SELECT 
    m.country_id, 
    m.season,
    m.home_goal,
    m.away_goal,
    AVG(m.home_goal + m.away_goal)  AS avg_goal
FROM match AS m;

However, I got this error

column "m.country_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:  m.country_id,

My question is: why was GROUP BY clause required? Why couldn't SQL know how to take average of two columns row by row?

Thank you.

Answer 1

try this:

SELECT 
    m.country_id, 
    m.season,
    m.home_goal,
    m.away_goal,
    (m.home_goal + m.away_goal)/2  AS avg_goal
FROM match AS m;

You have been asked for the group_by as avg() much like sum() work on multiple values of one column where you classify all columns that are not a columns wise operation in the group by

You are looking to average two distinct columns - it is a row-wise operations instead of column-wise

Answer 2

how to take average of two columns row by row?

You don't use AVG() for this; it is an aggregate function, that operates over a set of rows. Here, it seems like you just want a simple math computation:

SELECT 
    m.country_id, 
    m.season,
    m.home_goal,
    m.away_goal,
    (m.home_goal + m.away_goal) / 2.0 AS avg_goal
FROM match AS m;

Note the decimal denominator (2.0): this avoids integer division in databases that implement it.

Answer 3

Avg in the context of the function mentioned above is calculating the average of the values of the columns and not the average of the two values in the same row. It is an aggregate function and that’s why the group by clause is required.

In order to take the average of two columns in the same row you need to divide by 2.

Answer 4

Let's consider the following table:

CREATE TABLE Numbers([x] int, [y] int, [category] nvarchar(10));

INSERT INTO Numbers ([x], [y], [category])
VALUES
    (1, 11, 'odd'),
    (2, 22, 'even'),
    (3, 33, 'odd'),
    (4, 44, 'even');

Here is an example of using two aggregate functions - AVG and SUM - with GROUP BY:

SELECT
  Category,
  AVG(x) as avg_x,
  AVG(x+y) as avg_xy,
  SUM(x) as sum_x,
  SUM(x+y) as sum_xy
FROM Numbers
GROUP BY Category
  

The result has two rows:

Category    avg_x   avg_xy  sum_x   sum_xy
even    3   36  6   72
odd     2   24  4   48

Please note that Category is available in the SELECT part because the results are GROUP BY'ed by it. If a GROUP BY is not specified then the result would be 1 row and Category is not available (which value should be displayed if we have sums and averages for multiple rows with different caetories?).

What you want is to compute a new column and for this you don't use aggregate functions:

SELECT
  (x+y)/2 as avg_xy,
  (x+y) as sum_xy
FROM Numbers

This returns all rows:

avg_xy  sum_xy
6   12
12  24
18  36
24  48

---

If your columns are integers don't forget to handle rounding, if needed. For example (CAST(x AS DECIMAL)+y)/2 as avg_xy,

Answer 5

The simple arithmetic calculation:

(m.home_goal + m.away_goal) / 2.0

is not exactly equivalent to AVG(), because NULL values mess it up. Databases that support lateral joins provide a pretty easy (and efficient) way to actually use AVG() within a row.

The safe version looks like:

(coalesce(m.home_goal, 0) + coalesce(m.away_goal, 0)) /
nullif( (case when m.home_goal is not null then 1 else 0 end +
         case when m.away_goal is not null then 1 else 0 end
        ), 0
      )

Some databases have syntax extensions that allow the expression to be simplified.