Find the average of two combined columns in sql
Solution 1
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)
.
Solution 2
To use the avg function,
SELECT avg(col1 + col2)
FROM test
WHERE uid=5;
Solution 3
SELECT avg(col1 + col2) as avgtotal
FROM test
WHERE uid=5
Solution 4
i got my answer here , so i will add this note which may help others:
1.avg(col1+col2) as avg_col1_plus_col2,
2.avg(col1) + avg(col2) as avg_col1_plus_avg_col2,
3.avg(col1+col2)/2 as avgTotal1,
4.avg(col1)/2+avg(col1)/2 as avgTotal2
sentence 1 is equal to sentence 2 as eggyal explained,grammar is ok but logically its not the result that we want, so we need to divide the average by columns numbers as in sentence 3 and 4.
ak85
mainly a frontend developer trying to learn more about php and mysql SOreadytohelp
Updated on July 26, 2022Comments
-
ak85 almost 2 years
I want to find the avg of the total of two columns. I want to count the total of col1 and the total of col2 then find the average(how many different rows they are in).
I have managed to come up with a solution in the this sqlfiddle (also see below) is this the best way? I initially thought I would need to use the avg function but couldn't work it out using this.
CREATE TABLE test ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, uid INT, col1 INT, col2 INT ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB; INSERT INTO test (id, uid, col1, col2) VALUES (1,5,8,12), (2,1,2,3), (3,1,2,33), (4,5,25,50), (5,5,22,3); ( SELECT ((sum(col1) + sum(col2))/count(*)) FROM test WHERE uid=5 )
-
luckystars about 11 yearsWhy use group by? The uid is primary key.
-
luckystars about 11 yearsSo SELECT avg(col1) + avg(col2) FROM test WHERE uid=5 should work.
-
Orangecrush about 11 yearsMy bad. Was trying something else and forgot to take that off. Edited answer now.
-
Ben about 7 yearsThis is so close, and it's a great approach, but it's incorrect. The sum of two averages is not the average of two sums. The sum of two averages divided by 2 is the average of two sums. consider this pseudocode:
avg(1,2,3) + avg(1,2,3) == 4, but avg(1,2,3,3,2,1) == 2 == avg(avg(1,2,3),avg(1,2,3)) == (avg(1,2,3) + avg(1,2,3)) / 2
-
eggyal about 7 years@Ben: none of your cases that equal 2 appear in my answer.
-
DependencyHell about 5 yearsmissing closing parenthesis