CODEWITHSUNDEEP
sqlsql-server-2005t-sqlsql-server-2008
Peter
Peter

Reputation: 651

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.

Upvotes: 36

Views: 168925

Answers (9)

henrik242
henrik242

Reputation: 386

I found this answer very useful: https://stackoverflow.com/a/6360077/866173

By modifying it a little, and making use of the common table query technique, I was able to achieve the average of a number of columns in millions of rows with many null values.

with unnested as (
    select id,
           unnest(array [col1, col2, col3, col4, col5]) as unnest
    from
        some_table
    order by id
)
select
    id,
    avg(unnest) the_average
from
    unnested
group by id;

Upvotes: 0

David R
David R

Reputation: 1

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

Upvotes: 0

imok1948
imok1948

Reputation: 91

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

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

SQL AVG with column names

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

Upvotes: 0

Wayne Workman
Wayne Workman

Reputation: 541

This works in MariaDB:

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

Upvotes: 0

user3192731
user3192731

Reputation: 1

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

Upvotes: -4

Bob S
Bob S

Reputation: 39

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;

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453287

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

Upvotes: 50

beetree
beetree

Reputation: 941

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

Upvotes: 3

anon
anon

Reputation:

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

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

Upvotes: 23

Related Questions