Geek asks and answers

Find the average of two combined columns in sql

I want to find avg from the total of two columns. I want to calculate the total number of col1 and the total number of col2, then find the average (how many different lines they are in).

I managed to find a solution in this sqlfiddle (see below), is this the best way? Initially, I thought that I would need to use the avg function, but I could not use it 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
    )
+5
source share
6 answers

By definition, AVG(col1) = SUM(col1)/COUNT(*)and AVG(col2) = SUM(col2)/COUNT(*)therefore (SUM(col1)+SUM(col2))/COUNT(*)= AVG(col1) + AVG(col2).

Furthermore, commutativity gives us (SUM(col1)+SUM(col2))/COUNT(*) = SUM(col1+col2)/COUNT(*)and therefore AVG(col1+col2).

+6
source

To use the avg function,

SELECT avg(col1 + col2)
FROM test
WHERE uid=5;

SQLFIDDLE DEMO

+2

Is this what you are looking for?

SELECT avg(col1 + col2)
FROM test
where uid = 5
group by uid
+1
source

SELECT avg (col1 + col2) as avgtotal

FROM test WHERE uid = 5

+1
source

I got my answer here, so add this note that 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 explained eggyal, the grammar is fine, but logically this is not the result we want, so we need to divide the average by the column numbers, as in sentences 3 and 4.

+1
source 
SELECT ((SUM(col1) + SUM(col2)) / (COUNT(col1) + COUNT(col2)) as a
FROM test
WHERE uid=5;
0
source

More articles:


All Articles