Average of multiple columns

Question

I have a table called Request and the data looks like:

Req_ID    R1   R2   R3   R4   R5

R12673    2    5    3    7    10
R34721    3    5    2    1    8
R27835    1    3    8    5    6

Now I want to display the average of R1,R2,R3,R4 and R5

So I wrote a query like:

Select Req_ID, Avg(R1+R2+R3+R4+R5) as Average
from Request
Group by Req_ID

But I just get the sum of R1,R2,R3,R4 and R5 not the average? Where am I doing wrong.

Scribner

AVG works on rows, not across columns. What do you expect the answer to be? – Slaveholder

Answer 1

If the data is stored as INT, you may want to try

Average = (R1 + R2 + R3 + R4 + R5) / 5.0

Slaveholder

R1,R2,R3,R4 and R5 all are of real datatype – Scribner

Keep in mind that NULL values could be a problem. Check @martin-smith solution – Commander

@Commander so what is the average of 3.2 and NULL? I would expect a result of NULL unless explicit requirements are stated otherwise. – Slaveholder

Answer 2

You don't mention if the columns are nullable. If they are and you want the same semantics that the AVG aggregate provides you can do (2008)

SELECT *,
       (SELECT AVG(c)
        FROM   (VALUES(R1),
                      (R2),
                      (R3),
                      (R4),
                      (R5)) T (c)) AS [Average]
FROM   Request  

The 2005 version is a bit more tedious

SELECT *,
       (SELECT AVG(c)
        FROM   (SELECT R1
                UNION ALL
                SELECT R2
                UNION ALL
                SELECT R3
                UNION ALL
                SELECT R4
                UNION ALL
                SELECT R5) T (c)) AS [Average]
FROM   Request

Attenuation

Martin Smith - would you please explain the significance of T (c) in the solution you have provided? – Corkboard

@AlokShenoy T is the alias of the derived table and c the alias of its single column. – Attenuation

Ah! Thank you for the explanation. – Corkboard

Any ideas how to omit NULL values when using the interesting VALUES syntax? I am not talking about replacing NULL with 0, but omitting them as they should not distort the AVG. – Jurisdiction

@Jurisdiction - This already happens. SELECT AVG(c) FROM (VALUES(1.0), (2.0), (NULL), (NULL), (NULL)) T (c) gives 1.5 not 0.6 – Attenuation

Answer 3

You could simply do:

Select Req_ID, (avg(R1)+avg(R2)+avg(R3)+avg(R4)+avg(R5))/5 as Average
from Request
Group by Req_ID

Right?

I'm assuming that you may have multiple rows with the same Req_ID and in these cases you want to calculate the average across all columns and rows for those rows with the same Req_ID

Abominate

@Aaron Bertrand I don't know if it's what the OP wants, granted, but I see no syntactic reason that GROUP BY can't be there... – Discontented

Oh, but if Req_ID is unique in the table why do you have the Group by? Doesn't make sense...? – Abominate

@Abominate I have used group by since I am using AVG function – Scribner

Answer 4

In PostgreSQL, to get the average of multiple (2 to 8) columns in one row just define a set of seven functions called average(). Will produce the average of the non-null columns.

And then just

select *,(r1+r2+r3+r4+r5)/5.0,average(r1,r2,r3,r4,r5) from request;
 req_id | r1 | r2 | r3 | r4 | r5 |      ?column?      |      average
--------+----+----+----+----+----+--------------------+--------------------
 R12673 |  2 |  5 |  3 |  7 | 10 | 5.4000000000000000 | 5.4000000000000000
 R34721 |  3 |  5 |  2 |  1 |  8 | 3.8000000000000000 | 3.8000000000000000
 R27835 |  1 |  3 |  8 |  5 |  6 | 4.6000000000000000 | 4.6000000000000000
(3 rows)

update request set r4=NULL where req_id='R34721';
UPDATE 1

select *,(r1+r2+r3+r4+r5)/5.0,average(r1,r2,r3,r4,r5) from request;
 req_id | r1 | r2 | r3 | r4 | r5 |      ?column?      |      average
--------+----+----+----+----+----+--------------------+--------------------
 R12673 |  2 |  5 |  3 |  7 | 10 | 5.4000000000000000 | 5.4000000000000000
 R34721 |  3 |  5 |  2 |    |  8 |                    | 4.5000000000000000
 R27835 |  1 |  3 |  8 |  5 |  6 | 4.6000000000000000 | 4.6000000000000000
(3 rows)

select *,(r3+r4+r5)/3.0,average(r3,r4,r5) from request;
 req_id | r1 | r2 | r3 | r4 | r5 |      ?column?      |      average
--------+----+----+----+----+----+--------------------+--------------------
 R12673 |  2 |  5 |  3 |  7 | 10 | 6.6666666666666667 | 6.6666666666666667
 R34721 |  3 |  5 |  2 |    |  8 |                    | 5.0000000000000000
 R27835 |  1 |  3 |  8 |  5 |  6 | 6.3333333333333333 | 6.3333333333333333
(3 rows)

Like this:

CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
    COUNT NUMERIC;
    TOTAL NUMERIC;
BEGIN
    COUNT=0;
    TOTAL=0;
    IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
    IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
    RETURN TOTAL/COUNT;
    EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
    COUNT NUMERIC;
    TOTAL NUMERIC;
BEGIN
    COUNT=0;
    TOTAL=0;
    IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
    IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
    IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
    RETURN TOTAL/COUNT;
    EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC,
V4 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
    COUNT NUMERIC;
    TOTAL NUMERIC;
BEGIN
    COUNT=0;
    TOTAL=0;
    IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
    IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
    IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
    IF V4 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V4; END IF;
    RETURN TOTAL/COUNT;
    EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC,
V4 NUMERIC,
V5 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
    COUNT NUMERIC;
    TOTAL NUMERIC;
BEGIN
    COUNT=0;
    TOTAL=0;
    IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
    IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
    IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
    IF V4 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V4; END IF;
    IF V5 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V5; END IF;
    RETURN TOTAL/COUNT;
    EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC,
V4 NUMERIC,
V5 NUMERIC,
V6 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
    COUNT NUMERIC;
    TOTAL NUMERIC;
BEGIN
    COUNT=0;
    TOTAL=0;
    IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
    IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
    IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
    IF V4 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V4; END IF;
    IF V5 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V5; END IF;
    IF V6 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V6; END IF;
    RETURN TOTAL/COUNT;
    EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC,
V4 NUMERIC,
V5 NUMERIC,
V6 NUMERIC,
V7 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
    COUNT NUMERIC;
    TOTAL NUMERIC;
BEGIN
    COUNT=0;
    TOTAL=0;
    IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
    IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
    IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
    IF V4 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V4; END IF;
    IF V5 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V5; END IF;
    IF V6 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V6; END IF;
    IF V7 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V7; END IF;
    RETURN TOTAL/COUNT;
    EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION AVERAGE (
V1 NUMERIC,
V2 NUMERIC,
V3 NUMERIC,
V4 NUMERIC,
V5 NUMERIC,
V6 NUMERIC,
V7 NUMERIC,
V8 NUMERIC)
RETURNS NUMERIC
AS $FUNCTION$
DECLARE
    COUNT NUMERIC;
    TOTAL NUMERIC;
BEGIN
    COUNT=0;
    TOTAL=0;
    IF V1 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V1; END IF;
    IF V2 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V2; END IF;
    IF V3 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V3; END IF;
    IF V4 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V4; END IF;
    IF V5 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V5; END IF;
    IF V6 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V6; END IF;
    IF V7 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V7; END IF;
    IF V8 IS NOT NULL THEN COUNT=COUNT+1; TOTAL=TOTAL+V8; END IF;
    RETURN TOTAL/COUNT;
    EXCEPTION WHEN DIVISION_BY_ZERO THEN RETURN NULL;
END
$FUNCTION$ LANGUAGE PLPGSQL;

Remuneration

This is probably what I would do. The stored procedures seems a lot more compact and easier to read than a long query. – Rudiment

Answer 5

This works in MariaDB:

SELECT Req_ID, (R1+R2+R3+R4+R5)/5 AS Average
FROM Request
GROUP BY Req_ID;

Ballou

Answer 6

If someone is looking for averages of multiple columns listed at once like

You can do it like SELECT AVG(SurfaceArea), AVG(Population) FROM country

You can do it like SELECT AVG(SurfaceArea) AS value1, AVG(Population) as value2 FROM country

Chante

Answer 7

You can try this:

select  Req_ID, avg(Avg_R) average from
(Select *
from Request
unpivot
(
Avg_R for R ("R1","R2","R3","R4","R5")
)
)
group by Req_ID

Haphazard

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community? – Unite

Answer 8

Select Req_ID, sum(R1+R2+R3+R4+R5)/5 as Average
from Request
Group by Req_ID;

Starch