MySQL – How to Calculate Average Value of a Row

MySQL

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

Best Answer

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 !!!

Related Solutions

Mysql Join to get multiple row value from

Perhaps something like this:

SELECT e.employee_id, e.first_name, e.last_name,
CONCAT(addr.address_one,',',addr.address_two,',',state) fulladdr,
CONCAT(ph.mobile',',ph.mobile_one,',',ph.phone) phonenumbers,
e.dob, e.maritial_id, e.sex_id, e.photo, s.sex_label
FROM employee e
INNER JOIN sex s ON e.sex_id = s.id
INNER JOIN address addr ON e.address_id = addr.id
INNER JOIN phone ph ON e.phone_id = ph.id;

MySQL Rows to Columns to HBase (using Sqoop)

If you are interested in returning all the values as columns, you need to be try something very adventurous. First look at your query

SELECT 
    CONCAT(id, '-', date),
    MAX(IF(`num` = 0, avg, NULL)) num0 
FROM table 
GROUP BY 
    id, 
    date;

If would be a big mess to get MySQL to execute it as a query by giving MySQL the query with each column formulated.

Perhaps you can get MySQL to concatenate the column values using GROUP_CONCAT. That function was designed for aggregation (or aggravation if you are the actual developer). You can take all the num values can display it as a column-separated listed of numbers like this:

SELECT 
    CONCAT(id, '-', date),
    GROUP_CONCAT(IF(`num` = 0, avg, 0))) numlist
FROM table 
GROUP BY 
    id, 
    date;

You can also change the list to be delimited by pipes instead of commas like this:

SELECT 
    CONCAT(id, '-', date),
    GROUP_CONCAT(IF(`num` = 0, avg, 0)) SEPARATOR '|') numlist
FROM table 
GROUP BY 
    id, 
    date;

The default maximum length of a GROUP_CONCAT is 1024.

You need to change that max length in the session using this:

SET group_concat_max_len = 10240;

before you issue your query.

Best Answer

Related Solutions

Mysql Join to get multiple row value from

MySQL Rows to Columns to HBase (using Sqoop)

Give it a Try !!!

Related Question