SQL has many built-in functions that can be used for counting and computation.
Syntax of the function
The syntax for the built-in SQL function is:
SELECT function (column) from table
Types of functions
In SQL, there are several kinds of basic function types and types. The basic types of functions are:
- Aggregate function
- Scalar function
aggregate function (Aggregate functions)
The operation of the Aggregate function targets a series of values and returns a single value.
Note: If a SELECT statement is used in many other expressions in the list of items in the SELECT statement, the select must use the GROUP by statement!
"Persons" table (used in most cases)
Name |
| Age
Adams, John. |
38 |
Bush, George. |
33 |
Carter, Thomas. |
28 |
Aggregate functions in MS Access
function |
Description |
AVG (column) |
Returns the average of a column |
COUNT (column) |
Returns the number of rows in a column (not including NULL values) |
COUNT (*) |
Returns the number of rows selected |
First (column) |
Returns the value of the first record in a specified field |
Last (column) |
Returns the value of the last record in the specified field |
MAX (column) |
Returns the highest value of a column |
MIN (column) |
Returns the lowest value of a column |
STDEV (column) |
|
STDEVP (column) |
|
SUM (column) |
Returns the sum of a column |
VAR (column) |
|
VARP (column) |
|
Aggregate functions in SQL Server
function |
Description |
AVG (column) |
Returns the average of a column |
Binary_checksum |
|
CHECKSUM |
|
Checksum_agg |
|
COUNT (column) |
Returns the number of rows in a column (not including null values) |
COUNT (*) |
Returns the number of rows selected |
COUNT (DISTINCT column) |
Returns the number of distinct results |
First (column) |
Returns the value of the first record in a specified field (SQLServer2000 not supported) |
Last (column) |
Returns the value of the last record in the specified field (SQLServer2000 not supported) |
MAX (column) |
Returns the highest value of a column |
MIN (column) |
Returns the lowest value of a column |
STDEV (column) |
|
STDEVP (column) |
|
SUM (column) |
Returns the sum of a column |
VAR (column) |
|
VARP (column) |
|
Scalar function
The operation of the Scalar function targets a single value and returns a single value based on the input value.
Scalar functions in MS Access
function |
Description |
UCASE (c) |
Convert a field to uppercase |
LCASE (c) |
Convert a field to lowercase |
MID (C,start[,end]) |
Extracting characters from a text field |
LEN (c) |
Returns the length of a text field |
INSTR (C,char) |
Returns the numeric position of the specified character in a text field |
Left (C,number_of_char) |
Returns the left part of a requested text field |
Right (C,number_of_char) |
Returns the right part of a requested text field |
ROUND (C,decimals) |
Rounding the specified number of decimal digits for a numeric field |
MOD (x, y) |
Returns the remainder of a division operation |
Now () |
Returns the current system date |
FORMAT (C,format) |
Change the way a field is displayed |
DATEDIFF (D,DATE1,DATE2) |
Used to perform date calculations |
SQL AVG function
- SQL functions
- SQL count ()
Definition and usage
The AVG function returns the average of a numeric column. NULL values are not included in the calculation.
SQL AVG () syntax
SELECT AVG (column_name) from table_name
SQL AVG () instance
We have the following "Orders" table:
o_id |
OrderDate |
Orderprice |
Customer |
1 |
2008/12/29 |
1000 |
Bush |
2 |
2008/11/23 |
1600 |
Carter |
3 |
2008/10/05 |
700 |
Bush |
4 |
2008/09/28 |
300 |
Bush |
5 |
2008/08/06 |
2000 |
Adams |
6 |
2008/07/21 |
100 |
Carter |
Example 1
Now, we want to calculate the average of the "Orderprice" field.
We use the following SQL statement:
SELECT AVG (Orderprice) as Orderaverage from Orders
The result set looks like this:
Example 2
Now, we want to find customers with Orderprice values above the Orderprice average.
We use the following SQL statement:
Select Customer from Orderswhere orderprice> (select AVG (orderprice) from Orders)
The result set looks like this:
Customer |
Bush |
Carter |
Adams |
SQL COUNT () function
The count () function returns the number of rows that match the specified criteria.
SQL count () syntax SQL COUNT (column_name) syntax
The count (column_name) function returns the number of values for the specified column (NULL does not count in):
SELECT COUNT (column_name) from table_name
SQL COUNT (*) syntax
The count (*) function returns the number of records in the table:
SELECT COUNT (*) from table_name
SQL COUNT (DISTINCT column_name) syntax
The COUNT (DISTINCT column_name) function returns the number of different values for the specified column:
SELECT COUNT (DISTINCT column_name) from table_name
Note: COUNT (DISTINCT) applies to ORACLE and Microsoft SQL Server, but not to Microsoft Access.
SQL COUNT (column_name) instance
We have the following "Orders" table:
o_id |
OrderDate |
Orderprice |
Customer |
1 |
2008/12/29 |
1000 |
Bush |
2 |
2008/11/23 |
1600 |
Carter |
3 |
2008/10/05 |
700 |
Bush |
4 |
2008/09/28 |
300 |
Bush |
5 |
2008/08/06 |
2000 |
Adams |
6 |
2008/07/21 |
100 |
Carter |
Now, we want to calculate the number of orders for the customer "Carter".
We use the following SQL statement:
SELECT COUNT (Customer) as Customernilsen from Orderswhere customer= ' Carter '
The result of the above SQL statement is 2 because there are 2 orders for customer Carter:
SQL COUNT (*) instance
If we omit the WHERE clause, such as this:
SELECT COUNT (*) as numberoforders from Orders
The result set looks like this:
This is the total number of rows in the table.
SQL COUNT (DISTINCT column_name) instance
Now, we want to calculate the number of different customers in the Orders table.
We use the following SQL statement:
SELECT COUNT (DISTINCT Customer) as Numberofcustomers from Orders
The result set looks like this:
This is the number of different customers (Bush, Carter, and Adams) in the Orders table.
SQL First () function
First () function
The first () function returns the value of the number one record in the specified field.
Tip: You can use the order BY statement to sort records.
SQL First () syntax
SELECT First (column_name) from table_name
SQL First () instance
We have the following "Orders" table:
o_id |
OrderDate |
Orderprice |
Customer |
1 |
2008/12/29 |
1000 |
Bush |
2 |
2008/11/23 |
1600 |
Carter |
3 |
2008/10/05 |
700 |
Bush |
4 |
2008/09/28 |
300 |
Bush |
5 |
2008/08/06 |
2000 |
Adams |
6 |
2008/07/21 |
100 |
Carter |
Now, we want to find the first value of the "Orderprice" column.
We use the following SQL statement:
SELECT First (Orderprice) as Firstorderprice from Orders
The result set looks like this:
SQL Last () function
Last () function
The last () function returns the value of the final record in the specified field.
Tip: You can use the order BY statement to sort records.
SQL last () syntax
SELECT last (column_name) from table_name
SQL last () instance
We have the following "Orders" table:
o_id |
OrderDate |
Orderprice |
Customer |
1 |
2008/12/29 |
1000 |
Bush |
2 |
2008/11/23 |
1600 |
Carter |
3 |
2008/10/05 |
700 |
Bush |
4 |
2008/09/28 |
300 |
Bush |
5 |
2008/08/06 |
2000 |
Adams |
6 |
2008/07/21 |
100 |
Carter |
Now, we want to find the last value of the "Orderprice" column.
We use the following SQL statement:
SELECT last (Orderprice) as Lastorderprice from Orders
The result set looks like this:
SQL MAX () function
MAX () function
The Max function returns the maximum value in a column. NULL values are not included in the calculation.
SQL MAX () syntax
SELECT MAX (column_name) from table_name
Note: MIN and MAX can also be used for text columns to get the highest or lowest values in alphabetical order.
SQL MAX () instance
We have the following "Orders" table:
o_id |
OrderDate |
Orderprice |
Customer |
1 |
2008/12/29 |
1000 |
Bush |
2 |
2008/11/23 |
1600 |
Carter |
3 |
2008/10/05 |
700 |
Bush |
4 |
2008/09/28 |
300 |
Bush |
5 |
2008/08/06 |
2000 |
Adams |
6 |
2008/07/21 |
100 |
Carter |
Now, we want to find the maximum value for the "Orderprice" column.
We use the following SQL statement:
SELECT MAX (Orderprice) as Largestorderprice from Orders
The result set looks like this:
SQL MIN () function
MIN () function
The Min function returns the minimum value in a column. NULL values are not included in the calculation.
SQL MIN () syntax
SELECT MIN (column_name) from table_name
Note: MIN and MAX can also be used for text columns to get the highest or lowest values in alphabetical order.
SQL MIN () instance
We have the following "Orders" table:
o_id |
OrderDate |
Orderprice |
Customer |
1 |
2008/12/29 |
1000 |
Bush |
2 |
2008/11/23 |
1600 |
Carter |
3 |
2008/10/05 |
700 |
Bush |
4 |
2008/09/28 |
300 |
Bush |
5 |
2008/08/06 |
2000 |
Adams |
6 |
2008/07/21 |
100 |
Carter |
Now, we want to find the minimum value for the "Orderprice" column.
We use the following SQL statement:
SELECT MIN (Orderprice) as Smallestorderprice from Orders
The result set looks like this:
SUM () function
The SUM function returns the total number of numeric columns (total).
SQL SUM () syntax
SELECT SUM (column_name) from table_name
SQL SUM () instance
We have the following "Orders" table:
o_id |
OrderDate |
Orderprice |
Customer |
1 |
2008/12/29 |
1000 |
Bush |
2 |
2008/11/23 |
1600 |
Carter |
3 |
2008/10/05 |
700 |
Bush |
4 |
2008/09/28 |
300 |
Bush |
5 |
2008/08/06 |
2000 |
Adams |
6 |
2008/07/21 |
100 |
Carter |
Now, we want to find the total number of "Orderprice" fields.
We use the following SQL statement:
SELECT SUM (Orderprice) as OrderTotal from Orders
The result set looks like this:
SQL GROUP by statement
Aggregate functions, such as SUM, often require the addition of a GROUP by statement.
GROUP by statement
The GROUP BY statement is used to combine aggregate functions to group result sets based on one or more columns.
SQL GROUP by syntax
SELECT column_name, Aggregate_function (column_name) from Table_namewhere column_name operator Valuegroup by column_name
SQL GROUP by instance
We have the following "Orders" table:
o_id |
OrderDate |
Orderprice |
Customer |
1 |
2008/12/29 |
1000 |
Bush |
2 |
2008/11/23 |
1600 |
Carter |
3 |
2008/10/05 |
700 |
Bush |
4 |
2008/09/28 |
300 |
Bush |
5 |
2008/08/06 |
2000 |
Adams |
6 |
2008/07/21 |
100 |
Carter |
Now we want to find the total amount (total order) for each customer.
We want to use GROUP by statements to group customers.
We use the following SQL statements:
SELECT Customer,sum (Orderprice) from Ordersgroup by Customer
The result set looks like this:
Customer |
SUM (Orderprice) |
Bush |
2000 |
Carter |
1700 |
Adams |
2000 |
It's great, isn't it?
Let's take a look at what happens if you omit GROUP by:
SELECT Customer,sum (orderprice) from Orders
The result set looks like this:
Customer |
SUM (Orderprice) |
Bush |
5700 |
Carter |
5700 |
Bush |
5700 |
Bush |
5700 |
Adams |
5700 |
Carter |
5700 |
The result set above is not what we need.
So why not use the SELECT statement above? This is explained as follows: The above SELECT statement specifies two columns (Customer and SUM (Orderprice)). SUM (Orderprice) returns a separate value (total for the "orderprice" column), and "Customer" returns 6 values (each corresponding to each row in the Orders table). Therefore, we do not get the right results. However, you have seen that the GROUP by statement solves this problem.
GROUP by more than one column
We can also apply the GROUP by statement to more than one column, just like this:
SELECT Customer,orderdate,sum (Orderprice) from Ordersgroup by customer,orderdate
SQL HAVING clause
- SQL Group by
- SQL UCase ()
HAVING clause
The addition of the HAVING clause in SQL is because the WHERE keyword cannot be used with the aggregate function.
SQL having syntax
SELECT column_name, Aggregate_function (column_name) from Table_namewhere column_name operator Valuegroup by Column_ Namehaving aggregate_function (column_name) operator value
SQL having instance
We have the following "Orders" table:
o_id |
OrderDate |
Orderprice |
Customer |
1 |
2008/12/29 |
1000 |
Bush |
2 |
2008/11/23 |
1600 |
Carter |
3 |
2008/10/05 |
700 |
Bush |
4 |
2008/09/28 |
300 |
Bush |
5 |
2008/08/06 |
2000 |
Adams |
6 |
2008/07/21 |
100 |
Carter |
Now we want to find customers with a total order amount of less than 2000.
We use the following SQL statement:
SELECT Customer,sum (Orderprice) from Ordersgroup by Customerhaving SUM (Orderprice) <2000
The result set is similar to:
Customer |
SUM (Orderprice) |
Carter |
1700 |
Now we want to find the customer "Bush" or "Adams" with more than 1500 of the total order amount.
We have added a common where clause to the SQL statement:
SELECT Customer,sum (orderprice) from Orderswhere customer= ' Bush ' OR customer= ' Adams ' GROUP by Customerhaving SUM ( Orderprice) >1500
Result set:
Customer |
SUM (Orderprice) |
Bush |
2000 |
Adams |
2000 |
SQL Date function
SQL Date
When we work on dates, the hardest task is to ensure that the date you insert is formatted to match the format of the date column in the database.
As long as the data contains only the date parts, running the query will not be a problem. However, if time is involved, the situation is a little more complicated.
Before we discuss the complexity of the date query, let's look at the most important built-in date processing functions.
MySQL Date function
The following table lists the most important built-in date functions in MySQL:
function |
Description |
Now () |
Returns the current date and time |
Curdate () |
Returns the current date |
Curtime () |
Returns the current time |
DATE () |
Extract date part of date or date/time expression |
EXTRACT () |
Returns a separate part of the date/time Press |
Date_add () |
Add a specified time interval to a date |
Date_sub () |
Subtract a specified time interval from a date |
DATEDIFF () |
Returns the number of days between two dates |
Date_format () |
Display Date/time in a different format |
SQL Server Date function
The following table lists the most important built-in date functions in SQL Server:
function |
Description |
GETDATE () |
Returns the current date and time |
DATEPART () |
Returns a separate part of the date/time |
DATEADD () |
Add or subtract a specified time interval in a date |
DATEDIFF () |
Returns a time between two dates |
CONVERT () |
Display Date/time in a different format |
SQL Date Data type
MySQL stores date or date/time values in the database using the following data types:
- DATE-Format YYYY-MM-DD
- DATETIME-format: Yyyy-mm-dd HH:MM:SS
- TIMESTAMP-format: Yyyy-mm-dd HH:MM:SS
- Year-format YYYY or YY
SQL Server uses the following data types to store date or date/time values in the database:
- DATE-Format YYYY-MM-DD
- DATETIME-format: Yyyy-mm-dd HH:MM:SS
- smalldatetime-format: Yyyy-mm-dd HH:MM:SS
- TIMESTAMP-Format: Unique number
SQL Date Processing
If the time part is not involved, then we can easily compare two dates!
Suppose we have the following "Orders" table:
OrderId |
ProductName |
OrderDate |
1 |
Computer |
2008-12-26 |
2 |
Printer |
2008-12-26 |
3 |
Electrograph |
2008-11-12 |
4 |
Telephone |
2008-10-19 |
Now, we want to select a record from the table above that OrderDate is "2008-12-26".
We use the following SELECT statement:
SELECT * from Orders WHERE orderdate= ' 2008-12-26 '
Result set:
OrderId |
ProductName |
OrderDate |
1 |
Computer |
2008-12-26 |
3 |
Electrograph |
2008-12-26 |
Now suppose that "Orders" is similar (note the time section in the "OrderDate" column):
OrderId |
ProductName |
OrderDate |
1 |
Computer |
2008-12-26 16:23:55 |
2 |
Printer |
2008-12-26 10:45:26 |
3 |
Electrograph |
2008-11-12 14:12:08 |
4 |
Telephone |
2008-10-19 12:56:10 |
If we use the SELECT statement above:
SELECT * from Orders WHERE orderdate= ' 2008-12-26 '
Then we won't get the result. This is because the query does not contain a date for the time part.
Tip: If you want to make queries simple and easier to maintain, don't use the time section in dates!
Database development Basics-sql Server aggregate functions, mathematical Functions, String functions, time-date functions