SQL > SQL Functions
Since we have started dealing with numbers, the next natural question to ask is if it is possible to do math on those numbers, such as summing them up or taking their average. The answer is yes! SQL has several arithematic functions, and they are:
- AVG: Average of the column.
- COUNT: Number of records.
- MAX: Maximum of the column.
- MIN: Minimum of the column.
- SUM: Sum of the column.
The syntax for using functions is,
SELECT "function type" ("column_name")
FROM "table_name";
FROM "table_name";
Examples of how these functions are used are presented individually in the next few pages.
In addition to using functions, it is also possible to use SQL to perform simple tasks such as addition (+) and subtraction (-). For character-type data, there are also several string functionsavailable, such as concatenation, trim, and substring functions. Different RDBMS vendors have different string functions implementations, and it is best to consult the references for your RDBMS to see how these functions are used.
SQL > SQL Functions > Average
SQL uses the AVG( ) function to calculate the average of a column. The syntax for using this function is,
SELECT AVG("column_name")
FROM "table_name";
FROM "table_name";
For example, if we want to get the average of all sales from the following table,
Table Store_Information
| Store_Name | Sales | Txn_Date |
| Los Angeles | 1500 | Jan-05-1999 |
| San Diego | 250 | Jan-07-1999 |
| Los Angeles | 300 | Jan-08-1999 |
| Boston | 700 | Jan-08-1999 |
we would type in,
SELECT AVG(Sales) FROM Store_Information;
Result:
| AVG(Sales) |
687.5 represents the average of all Sales entries: (1500 + 250 + 300 + 700) / 4.
SQL > SQL Functions > Count
Another arithmetic function is COUNT. This allows us to COUNT up the number of row in a certain table. The syntax is,
SELECT COUNT("column_name")
FROM "table_name";
FROM "table_name";
For example, if we want to find the number of store entries in our table,
Table Store_Information
| Store_Name | Sales | Txn_Date |
| Los Angeles | 1500 | Jan-05-1999 |
| San Diego | 250 | Jan-07-1999 |
| Los Angeles | 300 | Jan-08-1999 |
| Boston | 700 | Jan-08-1999 |
we'd key in,
SELECT COUNT (Store_Name)
FROM Store_Information;
FROM Store_Information;
Result:
| COUNT (Store_Name) |
COUNT and DISTINCT can be used together in a statement to retrieve the number of distinct entries in a table. For example, if we want to find out the number of distinct stores, we'd type,
SELECT COUNT (DISTINCT Store_Name)
FROM Store_Information;
FROM Store_Information;
Result:
| COUNT (DISTINCT Store_Name) |
SQL > SQL Functions > MAX Function
SQL uses the MAX function to find the maximum value in a column. The syntax for using theMAX function is,
SELECT MAX ("column_name")
FROM "table_name";
FROM "table_name";
For example, if we want to get the highest sales from the following table,
Table Store_Information
| Store_Name | Sales | Txn_Date |
| Los Angeles | 1500 | Jan-05-1999 |
| San Diego | 250 | Jan-07-1999 |
| Los Angeles | 300 | Jan-08-1999 |
| Boston | 700 | Jan-08-1999 |
we would type in
SELECT MAX (Sales) FROM Store_Information;
Result:
| MAX (Sales) |
1500 represents the maximum value of all Sales entries: 1500, 250, 300, and 700.
SQL > SQL Functions > MIN Function
SQL uses the MIN function to find the maximum value in a column. The syntax for using theMIN function is,
SELECT MIN ("column_name")
FROM "table_name";
FROM "table_name";
For example, if we want to get the lowest sales from the following table,
Table Store_Information
| Store_Name | Sales | Txn_Date |
| Los Angeles | 1500 | Jan-05-1999 |
| San Diego | 250 | Jan-07-1999 |
| Los Angeles | 300 | Jan-08-1999 |
| Boston | 700 | Jan-08-1999 |
we would type in
SELECT MIN(Sales) FROM Store_Information;
Result:
| MIN(Sales) |
250 represents the smallest value of all Sales entries: 1500, 250, 300, and 700.
SQL > SQL Functions > SUM Function
The SUM function is used to calculate the total for a column. The syntax is,
SELECT SUM("column_name")
FROM "table_name";
FROM "table_name";
For example, if we want to get the sum of all sales from the following table,
Table Store_Information
| Store_Name | Sales | Txn_Date |
| Los Angeles | 1500 | Jan-05-1999 |
| San Diego | 250 | Jan-07-1999 |
| Los Angeles | 300 | Jan-08-1999 |
| Boston | 700 | Jan-08-1999 |
we would type in,
SELECT SUM(Sales) FROM Store_Information;
Result:
| SUM(Sales) |
2750 represents the sum of all Sales entries: 1500 + 250 + 300 + 700.
SQL > SQL Functions > ROUND Function
The ROUND function in SQL is used to round a number to a specified precision. The syntax is:
ROUND (expression, [decimal place])
where [decimal place] indicates the number of decimal points returned. A negative number means the rounding will occur to the digit to the left of the decimal point. For example, -1 means the number will be rounded to the nearest tens.
Let's go through an example to see how the ROUND function is used. Let's assume we have the following table:
Table Student_Rating
| Column Name | Data Type |
| StudentID | integer |
| First_Name | char(20) |
| Rating | float |
and this table contains the following rows:
Table Student_Rating
| StudentID | First_Name | Rating |
| 1 | Jenny | 85.235 |
| 2 | Bob | 92.52 |
| 3 | Alice | 3.9 |
| 4 | James | 120.1 |
Example 1
SELECT First_Name, ROUND (Rating, 1) Rounded_Score FROM Student_Rating;
Result:
| First_Name | Rounded_Score |
| Jenny | 85.2 |
| Bob | 92.5 |
| Alice | 3.9 |
| James | 120.1 |
Example 2
SELECT First_Name, ROUND(Rating, -1) Rounded_Score FROM Student_Rating;
Result:
| First_Name | Rounded_Score |
| Jenny | 90 |
| Bob | 90 |
| Alice | 0 |
| James | 120 |
No comments:
Post a Comment