MySQL 查找sql中两个组合列的平均值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14787561/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find the average of two combined columns in sql
提问by ak85
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).
我想找到两列总数的平均值。我想计算 col1 的总数和 col2 的总数，然后找到平均值（它们在多少不同的行中）。
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.
我设法在这个sqlfiddle（也见下文）中提出了一个解决方案，这是最好的方法吗？我最初认为我需要使用 avg 函数，但无法使用它来解决。
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
)
回答by eggyal
By definition, AVG(col1) = SUM(col1)/COUNT(*)and AVG(col2) = SUM(col2)/COUNT(*), therefore (SUM(col1)+SUM(col2))/COUNT(*)= AVG(col1) + AVG(col2).
根据定义，AVG(col1) = SUM(col1)/COUNT(*)并且AVG(col2) = SUM(col2)/COUNT(*)，因此(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).
此外，加法的交换性给了我们(SUM(col1)+SUM(col2))/COUNT(*) = SUM(col1+col2)/COUNT(*)，因此AVG(col1+col2)。
回答by Orangecrush
To use the avg function,
要使用 avg 函数，
SELECT avg(col1 + col2)
FROM test
WHERE uid=5;
回答by romar
Is this what you are looking for?
这是你想要的？
SELECT avg(col1 + col2)
FROM test
where uid = 5
group by uid
回答by DevelopmentIsMyPassion
SELECT avg(col1 + col2) as avgtotal
选择 avg(col1 + col2) 作为 avgtotal
FROM test WHERE uid=5
从测试 WHERE uid=5
回答by Aha
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.
正如eggyal解释的那样，句子1等于句子2，语法还可以，但从逻辑上讲，这不是我们想要的结果，因此我们需要像句子3和4中一样将平均值除以列数。
回答by risafov
SELECT ((SUM(col1) + SUM(col2)) / (COUNT(col1) + COUNT(col2))) as a
FROM test
WHERE uid=5;
