SQL Advanced (10) function

SQL Advanced (10) function The SQL 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.

SELECT Customer,sum (Orderprice) from Ordersgroup by Customer

The result set looks like this:

Customer	SUM (Orderprice)
Bush	2000
Carter	1700
Adams	2000

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

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

The SQL have clause HAVING clause in SQL adds the HAVING clause because the WHERE keyword cannot be used with an 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.

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 UCASE () function SQL UCASE () syntax

SELECT UCASE (column_name) from table_name

SQL UCASE () instance
We have the following "Persons" table:
City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now we want to select the contents of the "LastName" and "FirstName" columns, and then convert the "LastName" column to uppercase.

SELECT UCASE (LastName) as Lastname,firstname from Persons

The result set looks like this:

LastName	FirstName
Adams	John
BUSH	George
CARTER	Thomas

SQL LCASE () function SQL LCASE () syntax

SELECT LCASE (column_name) from table_name

SQL LCASE () instance
We have the following "Persons" table:
City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now we want to select the contents of the "LastName" and "FirstName" columns, and then convert the "LastName" column to lowercase.

SELECT LCASE (LastName) as Lastname,firstname from Persons

The result set looks like this:

LastName	FirstName
Adams	John
Bush	George
Carter	Thomas

SQL mid () function mid () function
The MID function is used to extract characters from a text field.
SQL MID () syntax

SELECT MID (Column_name,start[,length]) from table_name

Parameters	Description
column_name	Necessary. The field to extract the characters from.
Start	Necessary. Specifies the starting position (the starting value is 1).
Length	Optional. The number of characters to return. If omitted, the MID () function returns the remaining text.

SQL MID () instance
We have the following "Persons" table:
City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now, we want to extract the first 3 characters from the City column.

SELECT MID (city,1,3) as smallcity from Persons

The result set looks like this:

Smallcity
Lon
New
Bei

The Len () function of the SQL len () function
The LEN function returns the length of the value in the Text field.
SQL LEN () syntax

SELECT LEN (column_name) from table_name

SQL LEN () instance we have the following "Persons" table:
City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Now, we want to get the length of the value in the City column.

SELECT LEN (city) as lengthofaddress from Persons

The result set looks like this:

Lengthofcity
6
8
7

The SQL ROUND () function ROUND () function ROUND function is used to round a numeric field to the specified number of decimal digits.
SQL ROUND () syntax

SELECT ROUND (column_name,decimals) from table_name

Parameters	Description
column_name	Necessary. The field to be rounded.
Decimals	Necessary. Specifies the number of decimal digits to return.

SQL ROUND () instance
We have the following "Products" table:

prod_id	ProductName	Unit	UnitPrice
1	Gold	+ G	32.35
2	Silver	+ G	11.56
3	Copper	+ G	6.85

Now, we want to round up the name and price to the nearest integer.

SELECT ProductName, ROUND (unitprice,0) as UnitPrice from products

The result set looks like this:

ProductName	UnitPrice
Gold	32
Silver	12
Copper	7

SQL now () function now () function
The NOW function returns the current date and time.
SQL Now () syntax

SELECT now () from table_name

SQL Now () instance
We have the following "Products" table:

prod_id	ProductName	Unit	UnitPrice
1	Gold	+ G	32.35
2	Silver	+ G	11.56
3	Copper	+ G	6.85

Now, we want to show the name and price of the day's date.

SELECT ProductName, UnitPrice, now () as Perdate from products

The result set looks like this:

ProductName	UnitPrice	perdate
Gold	32.35	12/29/2008 11:36:05 AM
Silver	11.56	12/29/2008 11:36:05 AM
Copper	6.85	12/29/2008 11:36:05 AM

The format function of the format () function of the SQL format () function is used for formatting the display of a field.
SQL FORMAT () syntax

SELECT FORMAT (Column_name,format) from table_name

Parameters	Description
column_name	Necessary. The field to format.
Format	Necessary. prescribed format.

SQL FORMAT () instance
We have the following "Products" table:

prod_id	ProductName	Unit	UnitPrice
1	Gold	+ G	32.35
2	Silver	+ G	11.56
3	Copper	+ G	6.85

Now, we want to show the name and price of each day's date (the date is displayed in the format "YYYY-MM-DD").

SELECT ProductName, UnitPrice, FORMAT (now (), ' Yyyy-mm-dd ') as Perdatefrom products

The result set looks like this:

ProductName	UnitPrice	perdate
Gold	32.35	12/29/2008
Silver	11.56	12/29/2008
Copper	6.85	12/29/2008

SQL Advanced (10) function