|
|
Group Functions
An SQL group function or aggregate functions performs an operation on a group of rows and returns a single result. You may want retrieve group of item-prices and return total-price. This type of scenario is where you would use a group function. The following table is summary of some SQL group functions & query examples.
| Function |
Description | Query Example
|
| AVG(fieldname) | Returns average value of a column | SELECT
avg(price)FROM inventory;
|
COUNT(fieldname)
COUNT(*) | Returns number of
items in table or queried items | SELECT
count(product_id)FROM product;
SELECT count(*) FROM product;
|
MAX(fieldname)
| Returns maximum value of a column
|
SELECT max(price)FROM inventory;
|
MIN(fieldname)
| Returns minum value of a column
| SELECT min(price)FROM inventory;
|
SUM(fieldname)
| Returns total value of a column
| SELECT sum(price)FROM inventory;
|
To use a group function in a SQL query, list the function name followed by numeric column name within parentheses. AVG averages the column, COUNT counts the number of items, MAX returns maximum number of the column, and MIN returns minimum number of the column.
The following is query to retrieve total price, average price, maximum price, and minimum price from the table "product" assuming the product table has the following values.
| Product ID | Name | Description | Price | Cost |
|---|
| 100000000 | Printer | Inkjet 300 colour
Printer | 120 | 80 |
| 100000001 | Printer | 1220CXI Inkjet Printer
| 200 | 130 |
| 100000002 | Printer | Photo 890 Injet Printer
| 250 | 200 |
| 100000003 | Printer | Photo 890 Injet Printer
| 300 | 270 |
SQL statements.
SELECT sum(price)
FROM product;
This statement will return the total amount for the column price which is 870.
SELECT avg(price)
FROM product;
This statement will return the average amount for the column price which is 870/4 or 217.50.
SELECT max(price)
FROM product;
This statement will return the maximum amount for the column price which is 300.
SELECT min(price)
FROM product;
This statement will return the minimum amount for the column price which is 120.
SELECT count(*)
FROM product;
This statement will return the number of items in table which is 4.
GROUP BY Clause with Group Functions
Group By is used to categorize the retrieved data. For example, you may wight want list sales of each product identified by product id. To do this, the following is Group By example that lists
|
Retrieve Data (Quiry)   Single-Row Functions |
|
|
About Clik | Contact
&nsbp;
