Average value of a row in MySQL

Question

I have a row which primary key value is 4 and there are another 8 values. Those are 45 78 89 54 98 14 19 70. I want to select the average value, ie 58.37 from this row. How do I do this ? The avg() function takes one argument only but i need to send 8 arguments.
My table picture is

Answer 1

Unlike the last question, there is no AVERAGE() function other than GROUP BY aggregation.

This one can only be done by Dynamic SQL

Here are the Steps

• Create the string bangla_1st+bangla_2nd+english_1st+english_2nd+...+science
• Count the number of columns in the table that hold grades
• Set up SQL to divide the sum of the columns by the count of the columns
• Execute the SQL

Here is the Dynamic SQL for it

#
# Create a List of Columns
# Separated by the Plus Sign
#
SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(column_name SEPARATOR '+') INTO @subject_sum
FROM information_schema.columns
WHERE table_schema=DATABASE() AND table_name='class6A'
AND column_name NOT IN ('roll','student_name');
#
# Count the Columns
#
SELECT COUNT(1) INTO @subject_count
FROM information_schema.columns
WHERE table_schema=DATABASE() AND table_name='class6A'
AND column_name NOT IN ('roll','student_name');
#
# Create the SQL
#
SET @sql = CONCAT('SELECT (',@subject_sum,')/',@subject_count);
SET @sql = CONCAT(@sql,' SubjectAverage FROM class6A WHERE roll=4');
#
# View the SQL
#
SELECT @sql Created_SQL_Statement\G
#
# Execute the SQL
#
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

GIVE IT A TRY !!!

Answer 2

There is a rather simpler solution. (Using information_schema can be dangerous, if there are thousands of tables in it and it can be slow at time)

I propose to use an actual function. The goal is to do a pivotal query (convert col to rows). This can be accomplish using UNION ALL. The PK of the table in question is passed to the function allowing it to calculate and return the Average.

The beauty about this is you can extend the function and do 95 Percentile and other type of Stats. You can use that function like other functions.

If a new column is added, all you need to do is add that column to the function. I think it's easier then to have to exclude the column you don't want.

CREATE FUNCTION `row_avg`(p_pk int ) RETURNS decimal(16,2)
BEGIN

DECLARE p_output decimal(16,2);

 select avg(A.rate) into  p_output 
 from (

select rate1 as 'rate' from rating where id = p_pk
union all
select rate2 as 'rate' from rating where id = p_pk
union all
select rate3 as 'rate' from rating where id = p_pk
union all
select rate4 as 'rate' from rating where id = p_pk
union all
select rate5 as 'rate' from rating where id = p_pk
union all
select rate6 as 'rate' from rating where id = p_pk
union all
select rate7 as 'rate' from rating where id = p_pk
) as A ;


  RETURN p_output;

END

table:

CREATE TABLE `rating` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role` int(11) DEFAULT NULL,
  `student_name` varchar(10) DEFAULT NULL,
  `rate1` tinyint(4) DEFAULT NULL,
  `rate2` tinyint(4) DEFAULT NULL,
  `rate3` tinyint(4) DEFAULT NULL,
  `rate4` tinyint(4) DEFAULT NULL,
  `rate5` tinyint(4) DEFAULT NULL,
  `rate6` tinyint(4) DEFAULT NULL,
  `rate7` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

Usage:

select row_avg(id) from test.rating ;
select row_avg(id) from test.rating where id  =3 ;

Enjoy !