Home Tutorialhelp Comment How to calculate average of a row?

 
 

Comment

Michal Borychowski
How to calculate average of a row?,
February 17, 2009 at 2:01 AM

Another interesting usage of COALESCE() function is to calculate average value of data stored in one row - if we allow to store NULL values there.

When we have this table (three items per row):
CREATE TABLE IF NOT EXISTS `voting` (
`opinion_id` mediumint(9) unsigned NOT NULL auto_increment,
`rank_0` tinyint(4) default NULL,
`rank_1` tinyint(4) default NULL,
`rank_2` tinyint(4) default NULL,
PRIMARY KEY (`opinion_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `voting` VALUES (1, 1, 2, NULL);
INSERT INTO `voting` VALUES (2, 3, NULL, 4);
INSERT INTO `voting` VALUES (3, 3, 1, 2);

We can use this SELECT statement to get average values stored in a row:

SELECT *,
(COALESCE(V.rank_0, 0)
+ COALESCE(V.rank_1, 0)
+ COALESCE(V.rank_2, 0))
/
(3 -
(COALESCE(V.rank_0 - V.rank_0, 1)
+ COALESCE(V.rank_1 - V.rank_1, 1)
+ COALESCE(V.rank_2 - V.rank_2, 1))
) AS row_avg FROM voting V


I talk about it in more detail on my blog post at <a href="http://tech-blog.borychowski.com/index.php/2009/02/mysql/average-value-in-a-row/" target="_blank">How to calculate average value of data in a row</a>
View All Comments
Advertisement
Related Tutorial and Articles
MySQL

MySQL Coalesce
 

MySQL

MySQL Average Command
 

Hibernate Questions

How to calculate the average in Hibernate?
 

Java Beginners

Calculate sum and Average in java
 

SQL Question

MYSQL - SQL
 

SQL Question

Calculate Month Difference in mysql - SQL
 

Hibernate Tutorial Example

Hibernate Criteria average example
 

SQL Question

mysql
 

SQL Question

MySQL - SQL
 

SQL Question

mysql - SQL
 

SQL Question

mysql - SQL
 

SQL Question

MySQl - SQL
 

Programming Tutorials

SQL Average Count
 

Java-Tutorials

MySQL NOT IN
 

Java-Tutorials

JDBC : Salary greater than average salary
 

SQL Tutorial

SQL Average Count
 

PHP Questions

mysql query
 

SQL Question

How to Import mysql database command line, Import MySQL dumpfile, SQL datafile into my database ?
 

SQL Question

MySQL or MSSQL - SQL
 

MySQL

MySQL Count
 

SQL Question

MYSQL
 

Java Beginners

calculate average
 

SQL Question

mysql table construction - SQL
 

JDBC

JDBC Example with MySQL
 

MySQL

SQL Average
 

SQL Question

mysql
 

Java Beginners

calculate average
 

SQL Question

MySql
 

SQL Question

mysql select statement - SQL
 

SQL Question

mysql - SQL
 

MySQL

Mysql Not
 

SQL Question

MySQL
 

Hibernate Questions

Hibernate criteria average Example
 

MySQL

MySQL Injection
 

SQL Tutorial

MySQL Area
 

SQL Question

mysql - SQL
 

JDBC Questions

how to connect mysql with JDBC - JDBC
 

Free SQL books

SQL Example, Codes and Tutorials
 

MySQL

MySQL Addition
 

SQL Question

mysql - SQL
 

MySQL

MySQL Front
 

PHP Questions

connectivity with mysql
 

SQL Question

how to use float datatype in mysql? - SQL
 

PHP Questions

php mysql fetch array example
 

MySQL

MySQL Tools
 

MySQL

MySQL by
 

SQL Question

How to make a connection from javaME with MySQL - SQL
 

PHP Questions

login page php mysql example
 

PHP Tutorial

PHP MySQL Introduction
 

SQL Tutorial

MySQL Addition
 

SQL Question

How to make a connection from javaME with MySQL - SQL
 

SQL Question

Difference between Mysql and SQL
 

MySQL

MySQL By Week
 

MySQL

MySQL Client
 

MySQL

MySQL Client
 

Programming Tutorials

MySQL How to Topic
 

JSP-Tutorials

MySQL allowMultiQueries JSP Example
 

JDBC Questions

persistence.xml for MySQL
 

MySQL

MySQL Arithmetic
 

MySQL

MySQL Attribute
 

SQL Question

How to get factorial in mysql
 

SQL Question

Create a counter in mySQL 5 database through Java - SQL
 

General

Welcome to the MySQL Tutorials
 

SQL Question

How to use between in mysql.
 

MySQL

MySQL Area
 

Java Beginners

Calculate an average Age of my Class?
 

MySQL

Introduction to MySQL
 

WebSevices Questions

mysql - WebSevices
 

SQL Question

Database - mysql - SQL
 

SQL Question

mysql select from multiple tables
 

SQL Question

sql - SQL
 

JDBC-Tutorials

Java Mysql Connection Example
 

MySQL

MySQL Examples, Learn MySQL with examples.
 

Training

MySQL Database Training
 

SQL Question

MySQL SpeedUp - SQL
 

PHP

PHP SQL Injection Example
 

JSP Tutorials

Connect JSP with mysql
 

SQL Question

Help on mySQL 5 command line - SQL
 

SQL Question

MySql function - SQL
 

MySQL

MySQL Cursor
 

MySQL

MySQL BIGINT
 

SQL Question

help on mySQL 5 command Line - SQL
 

MySQL

MySQL Dump Example
 

SQL Question

Derby vs MySQL - SQL
 

SQL Tutorial

SQL Date, SQL Date Examples
 

SQL Question

Inserting Data into mysql - SQL
 

Programming Tutorials

How to rename MySQL table?
 

SQL Question

Sql question
 

Java Tips and Examples

Average of Array
 

MySQL

MySQL
 

SQL Question

MySql database - SQL
 

Programming Tutorials

PDO MySQL
 

Programming Tutorials

Connecting to MySQL
 

SQL Question

MySQL take backup of a table - example code needed
 

SQL Tutorial

MySQL Aggregate Function
 

MySQL

Mysql Table
 

PHP Tutorial

MySql Query Example Tutorial
 

PHP Tutorial

PHP MySql Server Version
 

JDBC Questions

JAVA & MYSQL - JDBC
 

MySQL

MySQL rank
 

Advertisements Advertisements
 

 

 

DMCA.com