How to Select Avg of Multiple Columns on a Single Row

how do I select AVG of multiple columns on a single row

Try

 Select     (Coalesce(x,0) + Coalesce(y,0) + Coalesce(z,0)) /
       (Coalesce(x/x, 0) + Coalesce(y/y, 0) + Coalesce(z/z, 0))

or

 Select (Coalesce(x,0) + Coalesce(y,0) + Coalesce(z,0)) /
         (Case When x Is Null 0 Else 1 End +
          Case When y Is Null 0 Else 1 End +
          Case When z Is Null 0 Else 1 End)

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

SQL average multiple columns for each row with nulls

Instead of dividing by 3, use

(CASE WHEN Quality IS NULL THEN 0 ELSE 1 END + 
 CASE WHEN Schedule IS NULL THEN 0 ELSE 1 END + 
 CASE WHEN [Cost Control] IS NULL THEN 0 ELSE 1 END)

Average of multiple columns

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

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

Select multiple columns but apply Avg() with only one column?

You should use something called 'window functions'. They're similar to 'aggregate functions', but the act on every row (partitioning by some field value). I'm sure you will find out how to do it if you check the documentation. You're code will end up looking something like:

SELECT Program.Program_Name,
    Campus.Campus_Name,
    Program.Tution_Fees,
    AVG(Program.Tution_Fees) OVER(PARTITION BY Campus_ID) AS CAMPUS_AVERAGE
FROM Program
    INNER JOIN Campus
    ON Program.Campus_ID=Campus.Campus_ID
;

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

---

Related Topics