The COUNT() function returns the number of rows that matches a specified criterion.
The AVG() function returns the average value of a numeric column.
The SUM() function returns the total sum of a numeric column.
Let us see the syntax for each function below.
SELECT COUNT(column_name) FROM table_name WHERE condition; |
SELECT AVG(column_name) FROM table_name WHERE condition; |
SELECT SUM(column_name) FROM table_name WHERE condition; |
Below is a selection from the "Product" table in the DB2 database.
ProductID | ProductName | Category | Price | QtyOnHand | TotalValue |
---|---|---|---|---|---|
7001 | Mouse | Accessories | 75.00 | ||
7002 | Harddrive | 65.00 | 20 | 1,300 | |
7003 | Keyboard | Accessories | 36.00 | 33 | 1,118.00 |
7004 | RAM | Components | 23.50 | 16 | 376.00 |
7005 | Honda | Bikes | 1,200 | ||
7006 | PEN | 7.45 | 10 | 74.50 | |
7007 | Cddrive | Accessories | 75.00 | ||
7008 | Speaker | Accessories | 75.00 |
The following SQL statement finds the number of products in the "Product" table. NULL values are not counted.
SELECT COUNT(ProductID) FROM Products; |
COUNT(ProductID) |
---|
8 |
The following SQL statement finds the average price of all products in the "Product" table. NULL values are ignored.
SELECT AVG(Price) FROM Products; |
AVG(Price) |
---|
194.61875 |
The following SQL statement finds the sum of the "QtyOnHand" fields in the "Product" table. NULL values are ignored.
SELECT SUM(QtyOnHand) FROM Products; |
SUM(QtyOnHand) |
---|
79 |
If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!