Database development Basics-sql Server aggregate functions, mathematical Functions, String functions, time-date functions

Source: Internet
Author: User
Tags mathematical functions microsoft sql server scalar time interval types of functions

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) Age
Name
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:

Orderaverage
950
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
    • SQL avg ()
    • SQL First ()

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:

Customernilsen
2
SQL COUNT (*) instance

If we omit the WHERE clause, such as this:

SELECT COUNT (*) as numberoforders from Orders

The result set looks like this:

Numberoforders
6

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:

Numberofcustomers
3

This is the number of different customers (Bush, Carter, and Adams) in the Orders table.

SQL First () function
    • SQL count ()
    • SQL last ()
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:

Firstorderprice
1000
SQL Last () function
    • SQL First ()
    • SQL Max ()
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:

Lastorderprice

100

SQL MAX () function
    • SQL last ()
    • SQL min ()
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:

Largestorderprice
2000
SQL MIN () function
    • SQL Max ()
    • SQL sum ()
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:

Smallestorderprice
100
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:

OrderTotal
5700
SQL GROUP by statement
    • SQL sum ()
    • SQL have

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 View
    • SQL Nulls
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.