นายนู๋

ตัวอย่างตาราง

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

Products Table:

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.