Average of Multiple Columns

Average of multiple columns

If the data is stored as INT, you may want to try

Average = (R1 + R2 + R3 + R4 + R5) / 5.0

SQL Find Average Multiple Columns

If you are using MySQL, you can get the average -- ignoring NULLs -- using:

SELECT (COALESCE(`2.R.1`, 0) + COALESCE(`2.R.2`, 0) + COALESCE(`2.R.3`, 0) + COALESCE(`3.R.1`, 0) + COALESCE(`3.R.2`, 0) + COALESCE(`3.R.5`, 0)) /
       ((`2.R.1` IS NOT NULL) + (`2.R.2` IS NOT NULL) + (`2.R.3` IS NOT NULL) + (`3.R.1` IS NOT NULL) + (`3.R.2` IS NOT NULL) + (`3.R.5` IS NOT NULL)
       )
FROM standards_data
WHERE `uuid` = 4;

This calculates the average within each row. If you actually want the average across rows as well, you can use AVG() with the above as the argument.

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

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

Average Multiple Columns in Postgres

Try this

  select coalesce (PRICE_INSTORE, 0 ) +  coalesce (PRICE_PICKUP, 0) + coalesce (PRICE_DELIVERY , 0) + coalesce (PRICE_SHIP , 0)  / 
 ((case when PRICE_INSTORE is null then 0 else 1 end) + (case when PRICE_PICKUP is null then 0 else 1 end) +
 (case when PRICE_DELIVERY is null then 0 else 1 end) + (case when PRICE_SHIP is null then 0 else 1 end) )
 from product

Find the average of two combined columns in sql

By definition, AVG(col1) = SUM(col1)/COUNT(*) and AVG(col2) = SUM(col2)/COUNT(*), therefore (SUM(col1)+SUM(col2))/COUNT(*) = AVG(col1) + AVG(col2).

Also, the commutativity of addition gives us (SUM(col1)+SUM(col2))/COUNT(*) = SUM(col1+col2)/COUNT(*) and hence AVG(col1+col2).

How to get average of multiple columns in a table in Angular 10

To find the average, you can simply call getSum and then divide it by the number of rows. So, implementation would be like this:

getAverage(columnNumber) {
    let sum = getSum(columnNumber);
    let count = this.rural.length;
    return sum / count;
}

Or, in one line, return getSum(columnNumber)/this.rural.length;

weighted average aggregation on multiple columns of df

Change function for working by multiple columns and for avoid removing column for grouping are converting to MultiIndex:

def wavg(x, value, weight):
    d = x[value]
    w = x[weight]
    try:
        return (d.mul(w, axis=0)).div(w.sum())
    except ZeroDivisionError:
        return d.mean()

#columns used for groupby
groups = ["Group", "Year", "Month"]
#processing all another columns
cols = df.columns.difference(groups + ["Weight(kg)"], sort=False)

#create index and processing all columns by variable cols
df1 = (df.set_index(groups)
         .groupby(level=groups)
         .apply(wavg, cols, "Weight(kg)")
         .reset_index())
print (df2)
  Group  Year  Month    Calcium   Nitrogen
0     A  2020      1  28.000000   4.000000
1     A  2020      1  46.800000   2.400000
2     A  2021      5  36.000000   2.727273
3     A  2021      5  24.545455   3.636364
4     B  2021      8  90.000000  10.000000
5     C  2021      8  51.111111  11.111111
6     C  2021      8  42.222222   4.444444

---

Related Topics