Average from multiple columns per row in SQL-CodePudding

I want to do this in SQL

But the price 2 and price 3 come from another table and joined using sql too.

How to do that in sql?

CodePudding user response：

Here is a solution for your problem, please try this and see, this is a code sample if you're using a single table. Try to make necessary changes to join and pull fields from another table.

SELECT *,
 (SELECT AVG(tot) 
  FROM
  (VALUES(Price1),
         (Price2),
         (Price3)
  ) T (tot)) AS AverageOfColumns
FROM   MyTable;

Or this should also work, simple too, but if you have a large set of columns I would prefer the above one,

    select 
      Item
      ,sum(Price1 Price2 Price3)/3 
    from MyTable group by Item;

CodePudding user response：

select t1.item, t1.price1 , t2.price2, t2.price3, avg(t1.price1 , t2.price2, t2.price3) as avg from table1 as t1 inner join table2 as t2 on t1.item = t2.item