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. 开发者_开发百科
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
If the data is stored as INT
, you may want to try
Average = (R1 + R2 + R3 + R4 + R5) / 5.0
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
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;
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;
精彩评论