SQL Lesson 11. Summary Functions, Calculated Columns, and Views

Summary functions are also called statistical, aggregate or summarizing functions. These functions process a set of rows to count and return a single value. There are only five such functions:

• AVG () Function returns the average value of a column.


• COUNT () The function returns the number of rows in a column.


• MAX () Function returns the largest value in the column.


• MIN () Function returns the smallest value in a column.


• SUM () Function returns the sum of the column values.

We already met one of them - COUNT () - in lesson 8. Now let's get to know the rest. Suppose we wanted to know the minimum, maximum and average prices for books in our store. Then from the table Prices (prices) it is necessary to take the minimum, maximum and average values ​​for the price column. The request is simple:

SELECT MIN (price), MAX (price), AVG (price) FROM prices;

Now, we want to find out how much the supplier "Printing House" brought us the goods (id = 2). It is not so easy to compose such a request. Let's think about how to compose it:

1. First, from the Deliveries (incoming) table, select the identifiers (id_incoming) of those deliveries that were made by the "Printing House" supplier (id = 2):

2. Now from the table Magazine of deliveries (magazine_incoming) you need to select the goods (id_product) and their quantities (quantity), which were carried out in the deliveries found in paragraph 1. That is, the request from paragraph 1 becomes nested:

3. Now we need to add to the resulting table the prices for the products found, which are stored in the prices table. That is, we need to join the tables Magazine_incoming and Prices by the id_product column:

4. The resulting table clearly lacks the Sum column, that is calculated column... The ability to create such columns is provided in MySQL. To do this, you just need to specify in the query the name of the calculated column and what it should calculate. In our example, such a column will be called summa, and it will calculate the product of the quantity and price columns. The new column name is separated by the word AS:

SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product = prices.id_product AND id_incoming = (SELECT id_incoming FROM incoming WHERE 2 id_vendor

5. Great, all we have to do is to sum up the summa column and finally find out how much the supplier "Printing House" brought us the goods. The syntax for using the SUM () function is as follows:

SELECT SUM (column_name) FROM table_name;

We know the name of the column - summa, but we do not have the name of the table, since it is the result of the query. What to do? For such cases, MySQL provides Views. A view is a select query that is assigned a unique name and can be stored in a database for later use.

The syntax for creating a view is as follows:

CREATE VIEW view_name AS query;

Let's save our request as a view named report_vendor:

CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product = prices.id_product AND id_incoming = (FROMECT id_incoming = (FROMECT id_incoming WHERE );

6. Now you can use the final SUM () function:

SELECT SUM (summa) FROM report_vendor;

So we have achieved the result, although for this we had to use nested queries, joins, calculated columns and views. Yes, sometimes you have to think to get the result, without it anywhere. But we touched on two very important topics - calculated columns and views. Let's talk about them in more detail.

Calculated fields (columns)

Using an example, we looked at a mathematical calculated field today. Here I would like to add that you can use not only the operation of multiplication (*), but also subtraction (-), and addition (+), and division (/). The syntax is as follows:

SELECT column_1_name, column_2_name, column_1_name * column_2_name AS computed_column_name FROM table_name;

The second nuance is the AS keyword, we used it to set the name of the calculated column. In fact, this keyword is used to set aliases for any columns. Why is this needed? For shorter and more readable code. For example, our view might look like this:

CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity * B.price AS summa FROM magazine_incoming AS A, prices AS B WHERE A.id_product = B.id_product AND id_incoming = (SELECT id_incoming FROM incoming WHERE id_vendor = 2);

Agree that this is much shorter and clearer.

Representation

We have already covered the syntax for creating views. After creating views, they can be used in the same way as tables. That is, execute queries on them, filter and sort data, combine some views with others. On the one hand, it is very convenient way storing frequently used complex queries (as in our example).

Keep in mind, however, that views are not tables, that is, they do not store data, they only retrieve it from other tables. Hence, firstly, when the data in the tables changes, the results of the presentation will also change. And secondly, when a query is made to a view, the required data is searched, that is, the performance of the DBMS decreases. Therefore, they should not be abused.

This tutorial will show you how to use the SUM function in SQL Server (Transact-SQL) with Syntax and Examples.

Description

SQL Server (Transact-SQL) SUM function returns the total value of the expression.

Syntax

The syntax for the SUM function in SQL Server (Transact-SQL) is:

OR the syntax for the SUM function when grouping results by one or more columns is:

Parameters or arguments

expression1, expression2, ... expression_n are expressions that are not included in the SUM function and must be included in the GROUP BY clause at the end of the SQL statement.
aggregate_expression is the column or expression to be summed up.
tables - the tables from which you want to get records. There must be at least one table listed in the FROM clause.
WHERE conditions is optional. These are the conditions that must be met for the selected records.

Application

The SUM function can be used in the following versions SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Single field example

Consider some SQL examples See Server SUM Functions for an understanding of how to use the SUM function in SQL Server (Transact-SQL).

For example, you can find out how the total of all products, the number of which is greater than 10.

In this example of the SUM function, we set the SUM (quantity) expression to the "Total Quantity" alias. When returning a result set - "Total Quantity" will be displayed as the field name.

An example of using DISTINCT

You can use the DISTINCT operator in the SUM function. For example, the following SQL statement returns the total salary with unique salary values ​​where the salary is less than $ 29,000 per year.

If two salaries were $ 24,000 per year, only one of these values ​​would be used in the SUM function.

Example of using a formula

The expression contained in the SUM function does not have to be a single field. You can also use a formula. For example, you can calculate the total commission.

Transact-SQL

SELECT SUM (sales * 0.03) AS "Total Commission" FROM orders;

SELECT SUM (sales * 0.03) AS "Total Commission" FROM orders;

Example of using GROUP BY

In some cases, you will need to use the GROUP BY clause with the SUM function.

We will learn to summarize. No, these are not the results of studying SQL yet, but the results of the values ​​of the columns of the database tables. SQL aggregate functions act on column values ​​to produce a single result value. Most commonly used aggregate functions SQL SUM, MIN, MAX, AVG and COUNT. It is necessary to distinguish between two cases of using aggregate functions. First, aggregate functions are used by themselves and return a single resulting value. Second, aggregate functions are used with the SQL GROUP BY clause, that is, with grouping by fields (columns) to get the result values ​​in each group. Let's first consider the cases of using aggregate functions without grouping.

SQL SUM function

The SQL SUM function returns the sum of the values ​​of a column in a database table. It can only be applied to columns whose values ​​are numbers. SQL queries to get the resulting amount, start like this:

SELECT SUM (COLUMN_NAME) ...

This expression is followed by FROM (TABLE_NAME), and then a condition can be specified using the WHERE clause. In addition, DISTINCT can be specified in front of the column name, which means that only unique values ​​will be counted. By default, all values ​​are taken into account (for this, you can specifically specify not DISTINCT, but ALL, but the word ALL is optional).

If you want to execute queries to the database from this lesson on MS SQL Server, but this DBMS is not installed on your computer, then you can install it using the instructions at this link .

First, we will work with the company's database - Company1. The script for creating this database, its tables and filling the tables with data - in the file at this link .

Example 1. There is a database of the company with data on its divisions and employees. The Staff table, in addition to everything, has a column with data on employee salaries. The selection from the table is as follows (to enlarge the picture, click on it with the left mouse button):

To get the sum of all salaries, we use the following query (on MS SQL Server - with the prefix USE company1;):

SELECT SUM (Salary) FROM Staff

This query will return 287664.63.

And now . In the exercises, we are already starting to complicate the tasks, bringing them closer to those that are encountered in practice.

SQL MIN function

The SQL MIN function also works on columns whose values ​​are numbers and returns the minimum of all the values ​​in the column. This function has the same syntax as the SUM function.

Example 3. The database and table are the same as in example 1.

It is required to find out the minimum wage of employees of department number 42. To do this, write the following query (on MS SQL Server - with the prefix USE company1;):

The request will return the value 10505.90.

And again exercise for independent decision ... In this and some other exercises, you will need not only the Staff table, but also the Org table, which contains data about the divisions of the company:

Example 4. The Org table is added to the Staff table, which contains data about the divisions of the firm. Display the minimum number of years a single employee has worked in a department located in Boston.

SQL MAX function

The SQL MAX function works similarly and has a similar syntax, which is used when you need to determine the maximum value among all the values ​​in a column.

Example 5.

It is required to find out the maximum salary of employees of department number 42. To do this, write the following query (on MS SQL Server - with the preceding USE company1; construct):

The request will return the value 18352.80

It's time exercises for self-solution.

Example 6. We are working again with two tables - Staff and Org. Print the department name and the maximum commissions earned by one employee in a department belonging to the Eastern Division. Use JOIN (join tables) .

SQL AVG function

The above syntax for the previously described functions is also true for the SQL AVG function. This function returns the average of all values ​​in a column.

Example 7. The database and table are the same as in the previous examples.

Suppose you want to find out the average seniority of employees of department number 42. To do this, write the following query (on MS SQL Server - with the prefix USE company1;):

The result will be a value of 6.33

Example 8. We work with one table - Staff. Withdraw the average salary of employees with an experience of 4 to 6 years.

SQL COUNT function

The SQL COUNT function returns the number of records in a database table. If you specify SELECT COUNT (COLUMN_NAME) ... in the query, the result will be the number of records excluding those records in which the column value is NULL (undefined). If you use an asterisk as an argument and start a SELECT COUNT (*) ... query, the result will be the number of all records (rows) in the table.

Example 9. The database and table are the same as in the previous examples.

It is required to find out the number of all employees who receive commissions. The number of employees whose Comm column values ​​are not NULL will return the following query (on MS SQL Server - with the preceding USE company1; construct):

SELECT COUNT (Comm) FROM Staff

The result is 11.

Example 10. The database and table are the same as in the previous examples.

If you need to find out the total number of records in a table, then we use a query with an asterisk as an argument to the COUNT function (on MS SQL Server - with the preceding USE company1; construct):

SELECT COUNT (*) FROM Staff

The result is 17.

In the next self-help exercise you will need to use a subquery.

Example 11. We work with one table - Staff. Display the number of employees in the planning department (Plains).

Aggregate Functions with SQL GROUP BY (Grouping)

Now let's look at using aggregate functions in conjunction with the SQL GROUP BY clause. The SQL GROUP BY clause is used to group the result values ​​by the columns of the database table. The site has a lesson dedicated to this operator separately .

We will work with the "Ads Portal 1" database. The script for creating this database, its table and filling the data table is in the file at this link .

Example 12. So there is a classifieds portal database. It contains the Ads table, which contains data about the ads submitted for the week. The Category column contains data about large categories of ads (for example, Real Estate), and the Parts column contains data about the smaller parts included in the category (for example, the Apartments and Villas parts are parts of the Real Estate category). The Units column contains data on the number of ads submitted, and the Money column contains the amount of money received for submitting ads.

Category	Part	Units	Money
Transport	Motor vehicles	110	17600
The property	Apartments	89	18690
The property	Cottages	57	11970
Transport	Motorcycles	131	20960
Building materials	Planks	68	7140
Electrical engineering	TV sets	127	8255
Electrical engineering	Refrigerators	137	8905
Building materials	Regips	112	11760
Leisure	Books	96	6240
The property	Houses	47	9870
Leisure	Music	117	7605
Leisure	Games	41	2665

Using the SQL GROUP BY statement, find the amount of money earned from serving ads in each category. We write the following query (on MS SQL Server - with the preceding construction USE adportal1;):

SELECT Category, SUM (Money) AS Money FROM ADS GROUP BY Category

Example 13. The database and table are the same as in the previous example.

Use the SQL GROUP BY statement to find out which part of each category had the most ads. We write the following query (on MS SQL Server - with the preceding construction USE adportal1;):

SELECT Category, Part, MAX (Units) AS Maximum FROM ADS GROUP BY Category

The result will be the following table:

Total and individual values ​​in one table can be obtained combining query results using the UNION operator .

Relational databases and the SQL language

How can I find out the number of PC models produced by a particular vendor? How to determine the average price for computers that have the same specifications? These and many other questions related to some statistical information can be answered using summary (aggregate) functions... The standard provides for the following aggregate functions:

All of these functions return a single value. In this case, the functions COUNT, MIN and MAX are applicable to any data type, while SUM and AVG are used only for numeric fields. Difference between function COUNT (*) and COUNT (<имя поля>) is that the second does not take NULL values ​​into account when calculating.

Example. Find the minimum and maximum price for personal computers:

Example. Find the number of computers available from manufacturer A:

Example. If we are interested in the number of different models produced by manufacturer A, then the query can be formulated as follows (using the fact that in the Product table each model is recorded once):

Example. Find the number of different models available by manufacturer A. The query is similar to the previous one, in which it was required to determine the total number of models produced by manufacturer A. Here you also need to find the number of different models in the PC table (i.e. available for sale).

To ensure that only unique values ​​are used when obtaining statistical indicators, when argument of aggregate functions can be used DISTINCT parameter... Another parameter ALL is the default and assumes that all return values ​​in the column are counted. Operator,

If we need to get the number of PC models produced every manufacturer, you will need to use GROUP BY clause syntactically following WHERE clauses.

GROUP BY clause

GROUP BY clause used to define groups of output lines to which can be applied aggregate functions (COUNT, MIN, MAX, AVG, and SUM)... If this clause is missing and aggregate functions are used, then all columns with the names mentioned in SELECT should be included in aggregate functions, and these functions will be applied to the entire set of rows that satisfy the query predicate. Otherwise, all columns of the SELECT list, not included in aggregate functions, must be specified in GROUP BY clause... As a result, all output lines of the query are divided into groups characterized by the same combinations of values ​​in these columns. After that, aggregate functions will be applied to each group. Note that for GROUP BY all NULL values ​​are treated as equal, i.e. when grouped by a field containing NULL values, all such rows will fall into one group.
If if there is a GROUP BY clause, in the SELECT clause no aggregate functions, then the query will simply return one row from each group. This feature, along with the DISTINCT keyword, can be used to eliminate duplicate rows in the result set.
Let's take a look at a simple example:

SELECT model, COUNT (model) AS Qty_model, AVG (price) AS Avg_price FROM PC GROUP BY model;

In this request, for each PC model, their number and average cost are determined. All rows with the same model values ​​form a group, and the SELECT output calculates the number of values ​​and the average price values ​​for each group. The query will result in the following table:

model	Qty_model	Avg_price
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

If the SELECT had a column with a date, then it would be possible to calculate these indicators for each specific date. To do this, you need to add a date as a grouping column, and then aggregate functions would be calculated for each combination of values ​​(model-date).

There are several specific rules for performing aggregate functions:

• If as a result of the query no lines received(or more than one row for a given group), then the initial data for calculating any of the aggregate functions are absent. In this case, the result of executing the COUNT functions will be zero, and the result of all other functions will be NULL.
• Argument aggregate function cannot itself contain aggregate functions(function from function). Those. in one query, you cannot, say, get the maximum average values.
• The result of executing the COUNT function is integer(INTEGER). Other aggregate functions inherit the data types of the values ​​being processed.
• If, when executing the SUM function, a result was obtained that exceeds the maximum value of the used data type, error.

So, if the request does not contain GROUP BY clauses, then aggregate functions included in SELECT clause, are executed on all resulting query lines. If the request contains GROUP BY clause, each rowset that has the same column or column group values ​​specified in GROUP BY clause, constitutes a group, and aggregate functions are performed for each group separately.

HAVING clause

If WHERE clause defines a predicate for filtering rows, then HAVING offer applied after grouping to define a similar predicate filtering groups by values aggregate functions... This clause is needed to test the values ​​that are obtained using aggregate function not from separate lines of the record source defined in FROM clause, and from groups of such lines... Therefore, such a check cannot be contained in WHERE clause.

CALCULATIONS

Summary functions

SQL expressions often require preprocessing of data. For this purpose, special functions and expressions are used.

Quite often, you need to find out how many records match a particular query,what is the sum of the values ​​of a certain numeric column, its maximum, minimum and average values. For this, the so-called summary (statistical, aggregate) functions are used. Summary functions process recordsets specified by a WHERE clause, for example. If they are included in the list of columns following the SELECT statement, the resulting table will contain not only the columns of the database table, but also the values ​​calculated using these functions. The following islist of summary functions.

• COUNT (parameter ) - returns the number of records specified in the parameter. If you want to get the number of all records, you must specify an asterisk (*) as a parameter. If you specify a column name as a parameter, the function will return the number of records in which this column has values ​​other than NULL. To find out how many distinct values ​​a column contains, precede its name with the DISTINCT keyword. For instance:

SELECT COUNT (*) FROM Clients;

SELECT COUNT (Order_amount) FROM Clients;

SELECT COUNT (DISTINCT Order_amount) FROM Clients;

Attempting to execute the following query will result in an error message:

SELECT Region, COUNT (*) FROM Clients;

• SUM (parameter ) - returns the sum of the values ​​of the column specified in the parameter. The parameter can also be an expression containing the column name. For instance:

SELECT SUM (Order_Sum) FROM Clients;

This SQL expression returns a one-column, one-record table containing the sum of all defined values ​​in the Order_Sum column from the Customers table.

Suppose that in the source table, the values ​​in the Order_Amount column are expressed in rubles, and we want to calculate the total amount in dollars. If the current exchange rate is, for example, 27.8, then you can get the required result using the expression:

SELECT SUM (Order_Sum * 27.8) FROM Clients;

• AVG (parameter ) - returns the arithmetic average of all values ​​of the column specified in the parameter. The parameter can be an expression containing the column name. For instance:

SELECT AVG (Order_Sum) FROM Clients;

SELECT AVG (Order_Sum * 27.8) FROM Clients

WHERE Region<>"North_3west";

• MAX (parameter ) - returns the maximum value in the column specified in the parameter. The parameter can also be an expression containing the column name. For instance:

SELECT MAX (Amount_order) FROM Clients;

SELECT MAX (Order_Sum * 27.8) FROM Clients

W HERE Region<>"North_3west";

• MIN (parameter ) - returns the minimum value in the column specified in the parameter. The parameter can be an expression containing the column name. For instance:

SELECT MIN (Order_Sum) FROM Clients;

SELECT MIN (Amount_order * 27.8) FROM Clients

W HERE Region<>"North_3west";

In practice, it is often required to obtain a summary table containing the total, average, maximum and minimum values ​​of numeric columns. To do this, you should use grouping (GROUP BY) and summary functions.

SELECT Region, SUM (Order_Sum) FROM Customers

GROUP BY Region;

The result table for this query contains the names of the regions and the total (total) amount of orders of all customers from the respective regions (Fig. 5).

Now let's look at a request to get all the totals by region:

SELECT Region, SUM (Order_Sum), AVG (Amount_order), MAX (Amount_order), MIN (Order_Sum)

FROM Clients

GROUP BY Region;

The initial and final tables are shown in Fig. 8. In the example, only the Northwest region is represented in the original table by more than one record. Therefore, in the result table for it, different summary functions give different values.

Rice. 8. Summary table of order amounts by region

When using summary functions in the list of columns in the SELECT statement, the headings of the corresponding columns in the result table are Expr1001, Expr1002, etc. (or something similar, depending on the SQL implementation). However, you can set the headings for the values ​​of the summary functions and other columns as you like. To do this, it is enough to specify an expression of the form after the column in the SELECT statement:

AS column_header

The AS keyword (as) means that in the result table, the corresponding column must have the header specified after AS. The assigned title is also called an alias. The following example (Figure 9) sets aliases for all calculated columns:

SELECT Region,

SUM (Order_Sum) AS [Total Order Amount],

AVG (Order_Sum) AS [Average Order Amount],

MAX (Order_Sum) AS Maximum,

MIN (Order_Sum) AS Minimum,

FROM Clients

GROUP BY Region;

Rice. 9. Summary table of order amounts by region using column aliases

Aliases with multiple words separated by spaces are enclosed in square brackets.

Summary functions can be used in SELECT and HAVING clauses, but they cannot be used in WHERE clauses. One HAVING clause is similar to WHERE clause, but unlike WHERE clause it selects records in groups.

Let's say you want to determine which regions have more than one customer. For this purpose, you can use the following request:

SELECT Region, Count (*)

FROM Clients

GROUP BY Region HAVING COUNT (*)> 1;

Value handling functions

When working with data, you often have to process them (convert to the right kind): select a substring in a string, remove leading and trailing spaces, round a number, calculate the square root, determine the current time, etc. There are three types of functions in SQL:

• string functions;
• numeric functions;
• date-time functions.

String functions

String functions take a string as a parameter and return a string or NULL after processing it.

• SUBSTRING (FROM line start)- returns the substring resulting from the string specified as a parameter line . Substring starts with a character whose ordinal number is specified in the start parameter and has a length specified in the length parameter. Line characters are numbered from left to right, starting at 1. The square brackets here indicate only that the expression enclosed in them is optional. If the expression FOR length is not used, then the substring from Start to the end of the original line. Parameter values start and length should be chosen so that the desired substring is actually inside the original string. Otherwise, the SUBSTRING function will return NULL.

For instance:

SUBSTRING ("Dear Masha!" FROM 9 FOR 4) - returns "Masha";

SUBSTRING ("Dear Masha!" FROM 9) - returns "Masha!";

SUBSTRING ("Dear Masha!" FROM 15) —returns NULL.

You can use this function in an SQL statement, for example, like this:

SELECT * FROM Clients

WHERE SUBSTRING (Region FROM 1 FOR 5) = "North";

• UPPER (string ) - converts all characters of the specified string to uppercase.
• LOWER (line ) - converts all characters of the specified string to lowercase.
• TRIM (LEADING | TRAILING | BOTH ["character"] FROM string ) - removes the leading (LEADING), trailing (TRAILING) or both (BOTH) characters from the string. The default character to be deleted is a space (""), so you can leave it blank. This function is most often used to remove spaces.

For instance:

TRIM (LEADING "" FROM "the city of St. Petersburg") rotates the "city of St. Petersburg";

TRIM (TRALING "" FROM "city of Saint Petersburg") returns "city of Saint Petersburg";

TRIM (BOTH "" FROM "city of St. Petersburg") - returns "city of St. Petersburg";

TRIM (BOTH FROM "city of St. Petersburg") - returns "city of St. Petersburg";

TRIM (BOTH "r" FROM "city of St. Petersburg") - returns "city of St. Petersburg".

Among these functions, the most commonly used are SUBSTRING () and TRIM ().

Numeric functions

Numeric functions as a parameter can accept data of not only numeric type, but always return a number or NULL (undefined value).

• POSITION ( target String IN string) - searches for an occurrence of the target string in the specified string. If the search is successful, it returns the position number of its first character, otherwise - 0. If the target string has zero length (for example, the string ""), then the function returns 1. If at least one of the parameters is NULL, then NULL is returned. Line characters are numbered from left to right, starting from 1.

For instance:

POSITION ("e" IN "Hello everyone") - returns 5;

POSITION ("everyone" IN "Hello everyone") - returns 8;

POSITION ("" Hello everyone ") - returns 1;

POSITION ("Hello!" IN "Hello everyone") - Returns 0.

In the Customers table (see Fig. 1), the Address column contains, in addition to the city name, postcode, street name and other data. You may need to select records for customers who live in a specific city. So, if you want to select records related to clients living in St. Petersburg, you can use the following SQL query expression:

SELECT * FROM Clients

WHERE POSITION ("St. Petersburg" IN Address)> 0;

Note that this simple query for fetching data can be formulated differently:

SELECT * FROM Clients

WHERE Address LIKE "% Petersburg%";

• EXTRACT (parameter ) - extracts an item from a date-time value or from an interval. For instance:

EXTRACT (MONTH FROM DATE "2005-10-25") - returns 10.

• CHARACTER_LENGTH (string ) - returns the number of characters in a string.

For instance:

CHARACTER_LENGTH ("Hello everyone") - Returns 11.

• OCTET_LENGTH (string ) - returns the number of octets (bytes) in a string. Each Latin or Cyrillic character is represented by one byte, and a Chinese character is represented by two bytes.
• CARDINALITY (parameter ) - takes a collection of items as a parameter and returns the number of items in the collection (cardinal). A collection can be, for example, an array or a multiset containing elements of various types.
• ABS (number ) - returns the absolute value of a number. For instance:

ABS (-123) - returns 123;

ABS (2 - 5) - Returns 3.

• MO D (number1, number2 ) - returns the remainder of the integer division of the first number by the second. For instance:

MOD (5, h) - returns 2;

MOD (2, h) - Returns 0.

• LN (number ) - Returns the natural logarithm of a number.
• EXP (number) - returns the e number (base of natural logarithm to power of number).
• POWER (number1, number2 ) - returns number1 number2 (number1 to the power of number2).
• SQRT (number ) - Returns the square root of a number.
• FLOOR (number ) - returns the largest integer not exceeding the specified parameter (rounding down). For instance:

FLOOR (5.123) - Returns 5.0.

• CEIL (number) or CEILING (number ) - returns the smallest integer that is not less than the rounding up specified by the parameter). For instance:

CEIL (5.123) - Returns 6.0.

• WIDTH_BUCKET (number1, number2, number3, number4) returns an integer in the range between 0 and number4 + 1. The number2 and number3 parameters specify a numeric segment divided into equal intervals, the number of which is specified by the number 4 parameter. The function determines the number of the interval in which the value falls number1. If number1 is outside the specified range, then the function returns 0 or number 4 + 1. For example:

WIDTH_BUCKET (3.14, 0, 9, 5) - Returns 2.

Date-time functions

There are three functions in SQL that return the current date and time.

• CURRENT_DATE - returns the current date (type DATE).

For example: 2005-06-18.

• CURRENT_TIME (number ) - returns the current time (TIME type). An integer parameter specifies the precision of the seconds. For example, if the value is 2, the seconds will be presented with hundredth precision (two digits in the fractional part):

12:39:45.27.

• CURRENT_TIMESTAMP (number ) - returns date and time (TIMESTAMP type). For example, 2005-06-18 12: 39: 45.27. An integer parameter specifies the precision of the seconds.

Note that the date and time returned by these functions are of non-character type. If you need to represent them as character strings, then you should use the conversion function of the type CAST ().

Date-time functions are commonly used in queries to insert, update, and delete data. For example, when you record information about sales in a specially provided column, you enter current date and time. After summing up the totals for the month or quarter, the sales data for the reporting period can be deleted.

Evaluated Expressions

Calculated expressions are built from constants (numeric, string, logical), functions, field names and other types of data by connecting them with arithmetic, string, logical and other operators. In turn, expressions can be combined by operators into more complex (compound) expressions. Parentheses are used to control the order in which expressions are evaluated.

Logical operators AND, OR and NOT and functions were discussed earlier.

Arithmetic operators:

• + - addition;
• - - subtraction;
• * - multiplication;
• / - division.

String operatoronly one is the string concatenation or concatenation operator (| |). Some SQL implementations (such as Microsoft Access) use the (+) symbol instead of (| |). The concatenation operator appends the second line to the end of the first example, expression:

"Sasha" | | "loves" | | "Masha"

will return the string "Sasha loves Masha" as a result.

When composing expressions, you must ensure that the operands of the operators have valid types. For example, the expression: 123 + "Sasha" is invalid because the arithmetic addition operator is applied to the string operand.

Evaluated expressions can appear after the SELECT statement, as well as in the conditional expressions of the WHERE and HAVI clauses NG.

Let's look at a few examples.

Suppose that the Sales table contains the columns Item_type, Quantity, and Price, and we need to know the revenue for each type of product. To do this, it is enough to include the expression Quantity * Price in the list of columns after the SELECT statement:

SELECT Item_type, Quantity, Price, Quantity * Price AS

Total FROM Sales;

It uses the AS (as) keyword to specify an alias for the calculated data column.

In fig. 10 shows the original Sales table and the resulting query table.

Rice. 10. Result of a query with the calculation of revenue for each type of product

If you need to find out the total revenue from the sale of all goods, then it is enough to apply the following query:

SELECT SUM (Quantity * Price) FROM Sales;

The following query contains evaluated expressions in both the column list and the WHERE clause. He selects from the sales table those goods, the sales proceeds of which are more than 1000:

SELECT Item_type, Quantity * Price AS Total

FROM Sales

WHERE Quantity * Price> 1000;

Suppose you want to get a table with two columns:

Product containing product type and price;

Total containing revenue.

Since it is assumed that in the original sales table, the Item_type column is character (of type CHAR), and the Price column is numeric, when merging (gluing) data from these columns, you must cast the numeric type to character using the CAST () function. The query performing this task looks like this (Fig. 11):

SELECT Item_type | | "(Price:" | | CAST (Price AS CHAR (5)) | | ")" AS Product, Quantity * Price AS Total

FROM Sales;

Rice. 11. The result of a query with combining different types of data in one column

Note. In Microsoft Access, a similar query will look like this:

SELECT Item_type + "(Price:" + C Str (Price) + ")" AS Product,

Quantity * Price AS Total

FROM Sales;

Conditional Expressions with CASE Statement

In ordinary programming languages ​​there are conditional jump operators that allow you to control the computational process depending on whether a certain condition is met or not. In SQL, such a statement is CASE (case, circumstance, instance). In SQL: 2003, this operator returns a value and therefore can be used in expressions. It has two main forms, which we will look at in this section.

CASE statement with values

The CASE statement with values ​​has the following syntax:

CASE check_value

WHEN value1 THEN result1

WHEN value2 THEN result2

. . .

WHEN value N THEN result N

ELSE result X

In the case when checked_value equal to value1 , the CASE statement returns the value result1 specified after the keyword THEN (then). Otherwise checked_value is compared with value2 , and if they are equal, then result2 is returned. Otherwise, the checked value is compared with the next value specified after the WHEN keyword (when), and so on. If checked_value is not equal to any of these values, then the value is returned result X specified after the ELSE keyword (otherwise).

The ELSE keyword is optional. If it is absent and none of the values ​​to be compared are equal to the tested value, then the CASE statement returns NULL.

Suppose, based on the Customers table (see Fig. 1), you want to get a table in which the names of the regions are replaced by their code numbers. If there are not too many different regions in the initial table, then to solve this problem it is convenient to use a query with the CASE operator:

SELECT Name, Address,

CASE Region

WHEN "Moscow" THEN "77"

WHEN "Tver region" THEN "69"

. . .

ELSE Region

AS Region code

FROM Clients;

CASE statement with search terms

The second form of the CASE operator assumes its use when searching in a table for those records that satisfy a certain condition:

CASE

WHEN condition1 THEN result1

WHEN result2 THEN result2

. . .

WHEN condition N THEN result N

ELSE result X

The CASE statement checks if condition1 is true for the first record in the set specified by the WHERE clause, or in the entire table if there is no WHERE. If so, CASE returns result1. Otherwise, condition 2 is checked for this record. If it is true, then the value result2 is returned, and so on. If none of the conditions is met, then the value is returned. X specified after the key word ELSE.

The ELSE keyword is optional. If it is absent and none of the conditions are met, the CASE statement rotates NULL. After the statement containing the CASE is executed for the first record, it moves to the next record. This continues until the entire recordset has been processed.

For example, in the book table (Title, Price), the column is NULL if the corresponding book is not available. The following query returns a table that displays the text "Out of stock" instead of NULL:

SELECT Name,

CASE

WHEN Price IS NULL THEN "Out of stock"

ELSE CAST (Price AS CHAR (8))

AS Price

FROM Books;

All values ​​in the same column must be of the same types. Therefore, this query uses the CAST function to cast the numeric values ​​in the Price column to a character type.

Note that instead of the first form of the CASE statement, you can always use the second:

CASE

WHEN test_value = value1 THEN result1

WHEN test_value = value2 THEN result2

. . .

WHEN checked_value = value N THEN resultN

ELSE resultX

NULLIF and COALESCE Functions

In some cases, especially in queries for updating data (UPDATE statement), it is convenient to use the more compact functions NULLIF () (NULL if) and COALESCE () (combine) instead of the cumbersome CASE statement.

NULLIF function ( value1, value2) returns NULL if the value of the first parameter matches the value of the second parameter, if it does not match, the value of the first parameter is returned unchanged. That is, if the equality value1 = value2 is satisfied, then the function returns NULL, otherwise - value value1.

This function is equivalent to the CASE statement in the following two forms:

• CASE value1

WHEN value2 THEN NULL

ELSE value1

• CASE

WHEN value1 = value2 THEN NULL

ELSE value1

COALESCE function ( value1, value2, ..., value N) accepts a list of values, which can be either definite or null. The function returns a specific value from the list, or NULL if all values ​​are undefined.

This function is equivalent to the following CASE statement:

CASE

WHEN value 1 IS NOT NULL THEN value 1

WHEN value 2 IS NOT NULL THEN value 2

. . .

WHEN value N IS NOT NULL THEN value N

ELSE NULL

For example, in the Books (Title, Price) table, the Price column is NULL if the corresponding book is not available. The following query returns a table in which, instead of NULL the text "Out of stock" is displayed:

SELECT Name, COALESCE (CAST (Price AS CHAR (8)),

"Out of stock") AS Price

FROM Books;