Average of multiple columns

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.

1

7 Answers

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

5

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

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

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

3

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;

1

This works in MariaDB:

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

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

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy