How can I find out the number of PC models produced by a particular supplier? How to determine the average price of computers with the same technical characteristics? These and many other questions related to some statistical information can be answered using final (aggregate) functions. The standard provides the following aggregate functions:

All these functions return a single value. At the same time, the functions COUNT, MIN And MAX 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 one does not take into account NULL values ​​when calculating.

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

Example. Find the available number of computers produced by 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 available different models produced 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., those 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 returned values ​​in the column are counted. Operator,

If we need to get the number of PC models produced everyone 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 that can be applied to aggregate functions (COUNT, MIN, MAX, AVG and SUM). If this clause is missing and aggregate functions are used, then all columns with names mentioned in SELECT, must 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 the GROUP BY clause. As a result, all output query rows are divided into groups characterized by the same combinations of values ​​in these columns. After this, aggregate functions will be applied to each group. Please note that for GROUP BY all NULL values ​​are treated as equal, i.e. when grouping 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 a result set.
Let's 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 value form a group, and the output of SELECT calculates the number of values ​​and average price values ​​for each group. The result of the query will be 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 date column, then it would be possible to calculate these indicators for each specific date. To do this, you need to add date as a grouping column, and then the 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 request no rows received(or more than one row for a given group), then there is no source data for calculating any of the aggregate functions. In this case, the result of 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 it is impossible, say, to obtain the maximum of average values.
• The result of executing the COUNT function is integer(INTEGER). Other aggregate functions inherit the data types of the values ​​they process.
• If the SUM function produces a result that is greater than the maximum value of the data type used, error.

So, if the request does not contain GROUP BY clauses, That aggregate functions included in SELECT clause, are executed on all resulting query rows. If the request contains GROUP BY clause, each set of rows that has the same values ​​of a column or group of columns specified in GROUP BY clause, makes up a group, and aggregate functions are performed for each group separately.

HAVING offer

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

The steps in this stage of learning SQL queries are designed to demonstrate the fact that SQL can not only make complex selections and sort data, but also calculate the results of mathematical functions, perform text transformation, group records, etc. More precisely, it’s not SQL that can do all this, but the ones that support it. SQL, with its standards, only formulates the requirements for these same DBMSs.

Step 15. Functions SUM, AVG, MIN, MAX, COUNT…

This step will show you how to use simple functions in SQL, such as sum, minimum and maximum values, average, etc. Let's start right away with an example of deriving the average length of service for all employees.

SELECT AVG(D_STAFF.S_EXPERIENCE) AS [AVERAGE EXPERIENCE OF EMPLOYEES] FROM D_STAFF

SQL function AVG.

Similarly, you can calculate the minimum and maximum values ​​(MIN, MAX), the total sum (SUM), etc. I advise you to try this using the training program. It is worth trying to define additional criteria for selecting records involved in determining the final value of a function using the WHERE clause.

The functions listed above use the entire query result to determine their value. Such functions are called aggregate . Also, there are a number of functions whose arguments are not all the values ​​of a column defined in the request, but each individual value of each individual row of the result. An example of such a function is the SQL function for calculating the length of a text field LEN:

SELECT S_NAME, LEN(D_STAFF.S_NAME) AS [LENGTH] FROM D_STAFF

Can be used superposition of SQL functions as shown below and calculate the maximum length value of the S_NAME field.

SELECT MAX(LEN(D_STAFF.S_NAME)) AS [MAXIMUM LENGTH] FROM D_STAFF

SQL function MAX.

Well, in conclusion, all together.

SELECT SUM(D_STAFF.S_EXPERIENCE) AS [SUM], AVG(D_STAFF.S_EXPERIENCE) AS [AVERAGE], MIN(D_STAFF.S_EXPERIENCE) AS [MINIMUM], MAX(D_STAFF.S_EXPERIENCE) AS [MAXIMUM], COUNT(*) AS [NUMBER OF RECORDS], MAX(LEN(D_STAFF.S_NAME)) AS [MAXIMUM LENGTH] FROM D_STAFF

An example of using aggregate SQL functions.

Notice the argument to the COUNT function. I specified (*) as an argument because I want to get the total number of records. If you specify, for example, COUNT(S_NAME), the result will be the number of non-empty S_NAME values ​​(S_NAME IS NOT NULL). It would be possible to write COUNT(DISTINCT S_NAME) and get the number of unique S_NAME values, but MS Access, unfortunately, does not support this option. In our example, COUNT(S_NAME) and COUNT(*) give exactly the same result.

Step 16: Converting Text

Often, text values ​​are filled in differently by software users: who writes the full name. with a capital letter, who is not; Some people write everything in capital letters. Many reporting forms require a unified approach, and not only reporting forms. To solve this problem, SQL has two functions UCASE and LCASE. An example of a request and the result of its processing are shown below:

SELECT UCASE(D_STAFF.S_NAME) AS , LCASE(D_STAFF.S_NAME) AS FROM D_STAFF

SQL functions UCASE and LCASE.

Step 17. SQL and working with strings

There is also a wonderful MID function that will help you solve the problem of extracting part of a string from the entire value of a text field. Here, too, the best comment would be an example - an example of “bullying” of the names of user profiles.

SELECT UCASE(MID(P_NAME,3,5)) FROM D_PROFILE

Superposition of SQL functions UCASE and MID.

We “cut out” 5 characters each from the profile name values, starting from the 3rd, and ended up with a bunch of repeating “garbage”. In order to leave only unique values, we will use the DISTINCT keyword.

SELECT DISTINCT UCASE(MID(P_NAME,3,5)) AS FROM D_PROFILE

Selecting unique aggregate function values.

Sometimes you have to use expressions with the LEN function as arguments to the MID function. In the next example, we already display the last 5 characters in the profile names.

SELECT UCASE(MID(P_NAME,LEN(P_NAME)-4,5)) FROM D_PROFILE

Using the SQL function LEN.

Step 18. Using SQL functions in the record selection criteria. HAVING operator

Having understood the functions, the question almost immediately arises: how can they be used in the criteria for selecting records? Some functions, namely those that are not aggregate functions, are quite easy to use. Here, for example, is a list of employees whose full name. more than 25 characters.

SELECT S_NAME FROM D_STAFF WHERE LEN(D_STAFF.S_NAME) > 25

Using the non-aggregate LEN function in SQL query conditions.

Well, if you, for example, need to display the identifiers of all positions that are occupied by more than one employee in the company, then this approach will not work. What I mean is that the following query may not make some sense, but it is incorrect from a structured query point of view. This is due to the fact that to correctly process such SQL queries using aggregate functions, one linear pass through employee records will not be enough.

SELECT S_POSITION FROM D_STAFF WHERE COUNT(S_POSITION)>1

For such cases, the HAVING keyword was introduced into SQL, which will help us solve the problem with positions and employees.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION HAVING COUNT(S_POSITION)>1

Using aggregate functions in SQL query conditions.

Step 19. Grouping data in SQL query results using the GROUP BY operator

The GROUP BY operator is needed to group the values ​​of aggregate functions by the values ​​of their associated fields. It is needed when we want to use the aggregate function value in the record selection criteria (previous step). It is also needed when we want to include the value of an aggregate function in the query result. But in its simplest form, grouping is equivalent to highlighting the unique values ​​of a column. Let's look at an example request.

SELECT S_POSITION FROM D_STAFF

And these are two options that allow you to display only unique S_POSITION values.

SELECT S_POSITION FROM D_STAFF GROUP BY S_POSITION

SELECT DISTINCT S_POSITION FROM D_STAFF

Well, now let’s return to grouping function values ​​by the values ​​of the fields associated with them. For each user profile, we will display the number of records associated with it in the D_STAFF_PROFILE table.

SELECT PROFILE_ID AS , COUNT(PROFILE_ID) AS [NUMBER OF RECORDS] FROM D_STAFF_PROFILE GROUP BY PROFILE_ID

Using SQL aggregate function together with grouping.

The GROUP BY operator also allows you to group the query result by more than one field, listing them separated by commas. I hope that after all of the above, no additional comments are needed on the result of the last query.

SELECT S.S_POSITION AS , S.S_NAME AS [EMPLOYEE], COUNT(SP.STAFF_ID) AS [NUMBER OF RECORDS IN TABLE D_STAFF_PROFILE] FROM D_STAFF S, D_STAFF_PROFILE SP WHERE S.XD_IID=SP.STAFF_ID GROUP BY S.S_POSITION, S. S_NAME

Grouping SQL query result rows by several fields.

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

SQL SUM function

The SQL SUM function returns the sum of the values ​​in a database table column. It can only be applied to columns whose values ​​are numbers. The SQL queries to get the resulting sum 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. Additionally, the column name can be preceded by DISTINCT, 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 not required).

Example 1. There is a company database with data about its divisions and employees. The Staff table also has a column with data on employee salaries. The selection from the table looks like this (to enlarge the picture, click on it with the left mouse button):

To obtain the sum of all salaries, use the following query:

SELECT SUM (Salary) FROM Staff

This query will return the value 287664.63.

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

SQL MIN function

The SQL MIN function also operates on columns whose values ​​are numbers and returns the minimum of all values ​​in the column. This function has a syntax similar to that of the SUM function.

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

We need to find out the minimum wage for employees of department number 42. To do this, write the following request:

The query will return the value 10505.90.

And again exercise for self-solution. In this and some other exercises, you will need not only the Staff table, but also the Org table, containing data about the company’s divisions:

Example 4. The Org table is added to the Staff table, containing data about the company's departments. Print the minimum number of years worked by one employee 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 values ​​in a column.

Example 5.

You need to find out the maximum salary of employees of department number 42. To do this, write the following request:

The query will return the value 18352.80

It's time exercises for independent solution.

Example 6. We again work with two tables - Staff and Org. Display the name of the department and the maximum value of the commission received by one employee in the department belonging to the group of departments (Division) Eastern. Use JOIN (joining tables) .

SQL AVG function

What is stated regarding the syntax for the previous functions described 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 length of service of employees of department number 42. To do this, write the following query:

The result will be 6.33

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

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 without taking into account 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) of the table.

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

You want to know the number of all employees who receive commissions. The number of employees whose Comm column values ​​are not NULL will be returned by the following query:

SELECT COUNT (Comm) FROM Staff

The result will be 11.

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

If you want to find out the total number of records in the table, then use a query with an asterisk as an argument to the COUNT function:

SELECT COUNT (*) FROM Staff

The result will be 17.

In the next exercise for independent solution 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

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

Example 12. There is a database of the advertisement portal. It has an Ads table containing data about ads submitted for the week. The Category column contains data about large ad categories (for example, Real Estate), and the Parts column contains data about smaller parts included in the categories (for example, the Apartments and Summer Houses parts are parts of the Real Estate category). The Units column contains data on the number of advertisements submitted, and the Money column contains data on the amount of money received for submitting advertisements.

Category	Part	Units	Money
Transport	Cars	110	17600
Real estate	Apartments	89	18690
Real estate	Dachas	57	11970
Transport	Motorcycles	131	20960
Construction materials	Boards	68	7140
Electrical engineering	TVs	127	8255
Electrical engineering	Refrigerators	137	8905
Construction materials	Regips	112	11760
Leisure	Books	96	6240
Real estate	At home	47	9870
Leisure	Music	117	7605
Leisure	Games	41	2665

Using the SQL GROUP BY statement, find the amount of money earned by posting ads in each category. We write the following request:

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.

Using the SQL GROUP BY statement, find out which part of each category had the most listings. We write the following request:

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

The result will be the following table:

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

Relational Databases and SQL Language