MySQL Aggregate Functions - SUM()
This lesson will teach you how to use the aggregate function SUM(). If you haven't already
read through Tizag's Aggregate Introduction Lesson, please check it out now. It explains concepts used in this lesson.
We will be using the "products" table again -- this time to display the use of MySQL's SUM function.
You can download the products.sql file from our website. If you are new to MySQL you will need to know how to Create a MySQL Table and Insert a MySQL Row.
Here's a visual of the "products" table.
Products Table:
id | name | type | price |
123451 | Park's Great Hits | Music | 19.99 |
123452 | Silly Puddy | Toy | 3.99 |
123453 | Playstation | Toy | 89.95 |
123454 | Men's T-Shirt | Clothing | 32.50 |
123455 | Blouse | Clothing | 34.97 |
123456 | Electronica 2002 | Music | 3.99 |
123457 | Country Tunes | Music | 21.55 |
123458 | Watermelon | Food | 8.73 |
MySQL SUM - Totaling Groups
SUM is an aggregate function that totals a specific column for a group. The "products" table that is displayed above has several products of various types. One use of SUM might be to find the total of all the items' price for each product type.
Just as we did in the aggregate introduction lesson, we are going to apply the aggregate function
to price and GROUP BY type to
create four groups: Music, Toy, Clothing and Food.
PHP and MySQL Code:
<?php
// Make a MySQL Connection
$query = "SELECT type, SUM(price) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "Total ". $row['type']. " = $". $row['SUM(price)'];
echo "<br />";
}
?>
Display:
Total Clothing = $67.47
Total Food = $8.73
Total Music = $45.53
Total Toy = $93.94
Download Tizag.com's MySQL Book
If you would rather download the PDF of this tutorial, check out our
MySQL eBook from the Tizag.com store.
You may also be interested in getting the PHP eBook Found Something Wrong in this Lesson?Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time! |