[sql] 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.

This question is related to sql sql-server-2005 tsql sql-server-2008

The answer is


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;

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

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


This works in MariaDB:

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

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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to sql-server-2008

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Get last 30 day records from today date in SQL Server How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904) SQL Server Service not available in service list after installation of SQL Server Management Studio How to delete large data of table in SQL without log?