CODEWITHSUNDEEP
sqldatabasepostgresqlaverage
AKIWEB
AKIWEB

Reputation: 19612

Average of two columns in SQL

I wanted to take average of two columns and display in a new column something like this:

+-+--+--+--+--------+--------+
|A|B |C |D |AVG(B/C)|AVG(C/B)|
+-+--+--+--+--------+--------+
|S|23|34|56|        |        |
+-+--+--+--+--------+--------+
|T|45|6 |79|        |        |
+-+--+--+--+--------+--------+

So, as shown above, I needed to take the each row values and perform B/C and then take the average accordingly to display it in a new column.

I wanted to do this in SQL query. Is it possible to perform this in a SQL command? I know the AVG() function does take the average of a column but how can I do B/C and then take the average? also if I need to take the average of B and C as well how can i do that.

This is what I am doing right now:

Select A,B,C,D FROM tableTest where A='S';

I now have to take the average of corresponding and also have another two columns additionally in the query to show the respective results.

Upvotes: 5

Views: 23248

Answers (3)

SendETHToThisAddress
SendETHToThisAddress

Reputation: 3704

You actually don't need to use AVG to get the average between 2 columns, you can do a math operation:

Select A,B,C,D,(B+C)/2 AS BCAvg
FROM tableTest 
WHERE A = 'S';

Which offers a slight advantage in simplicity, it doesn't require using the AVG function or a GROUP BY clause.

Upvotes: 0

martin bruijn
martin bruijn

Reputation: 11

AVG(col1/col2) is not the same as AVG((col1+col2/2).

How about using GROUP BY?

SELECT city, "average money spend:", AVG( sales-profit/ number-of-customer) FROM Sales GROUP BY City

Then better use

SUM(sales-profit)/SUM(number-of-customers)

or use

AVG(sales-profit)/AVG(number-of-customers)

(but this is less pragmatic (that is understandability is less).

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

You should give it a try:

SELECT A, AVG((B+C)/2) as bc, AVG((C+B)/2) as cb
FROM tableTest
WHERE A = 'S'
GROUP BY A

Upvotes: 4

Related Questions