MySQL Aggregate Functions - COUNT()

This lesson will teach you how to use the aggregate function COUNT(). If you missed the Aggregate Introduction Lesson, please check it out now, as it explains many concepts used in this lesson!


We will be using the "products" table that we constructed to display the use of MySQL's COUNT 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 first.
Below is the MySQL table "products".

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 COUNT - Counting Records

The COUNT function is an aggregate function that simply counts all the items that are in a group. The "products" table that is displayed above has several products of various types. One use of COUNT might be to find out how many items of each type there are in the table.
Just as we did in the aggregate introduction lesson, we are going to GROUP BY type to create four groups: Music, Toy, Clothing and Food. For a slight change of pace, let's count the name column to find how many products there are per type.

PHP and MySQL Code:

<?php
// Make a MySQL Connection

$query = "SELECT type, COUNT(name) FROM products GROUP BY type"; 
  
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
 echo "There are ". $row['COUNT(name)'] ." ". $row['type'] ." items.";
 echo "<br />";
}
?>

Display:

There are 2 Clothing items.
There are 1 Food items.
There are 3 Music items.
There are 2 Toy items.

Posted in: MySQL