Describes the use of arithmetic operators and the construction of calculated columns. The summary (aggregate) functions COUNT, SUM, AVG, MAX, MIN are considered. An example of using the GROUP BY clause for grouping in data selection queries is given. Describes the use of the HAVING clause.

Building calculated fields

In general, to create calculated (derived) field some expression should be specified in the SELECT list SQL language... These expressions use addition, subtraction, multiplication, and division arithmetic and built-in SQL functions. You can specify the name of any column (field) of a table or query, but only use the column name of that table or query that is listed in the FROM clause of the corresponding statement. When building complex expressions, parentheses may be needed.

SQL standards allow you to explicitly specify the column names of the result table by using the AS clause.

SELECT Product.Name, Product.Price, Deal.Quantity, Product.Price * Deal.Quantity AS Cost FROM Product INNER JOIN Deal ON Product.Product Code = Deal.Product Code Example 6.1. Calculating the total cost for each transaction.

Example 6.2. Get a list of companies with the names and initials of clients.

SELECT Company, Surname + "" + Left (First name, 1) + "." + Left (Middle name, 1) + "." AS Full name FROM Client Example 6.2. Obtaining a list of firms with the indication of the surname and initials of clients.

The query uses the built-in function Left, which allows you to cut one character from the left in a text variable in this case.

Example 6.3. Get a list of products with the year and month of sale.

SELECT Item.Name, Year (Deal.Date) AS Year, Month (Deal.Date) AS Month FROM Item INNER JOIN Deal ON Item.Product Code = Deal.Product Code Example 6.3. Receiving a list of products indicating the year and month of sale.

The query uses the built-in functions Year and Month to extract the year and month from a date.

Using summary functions

By using summary (aggregate) functions within the framework of a SQL query, you can get a number of summarizing statistics about the set of selected values ​​of the output set.

The following main ones are available to the user. summary functions:

• Count (Expression) - determines the number of records in the output set of the SQL query;
• Min / Max (Expression) - determine the smallest and largest of the set of values ​​in a certain field of the request;
• Avg (Expression) - This function allows you to calculate the average of a set of values ​​stored in a specific field of query-selected records. It is the arithmetic mean, i.e. the sum of the values ​​divided by their number.
• Sum (Expression) - calculates the sum of the set of values ​​contained in a specific field of the query-selected records.

Column names are most often used as expressions. The expression can also be calculated using the values ​​of several tables.

All of these functions operate on values ​​in a single column of a table or with an arithmetic expression and return a single value. The COUNT, MIN, and MAX functions apply to both numeric and non-numeric fields, while the SUM and AVG functions can only be used in the case of numeric fields, with the exception of COUNT (*). When calculating the results of any functions, all empty values ​​are first excluded, after which the required operation is applied only to the remaining specific column values. The COUNT (*) variant is a special case of using the COUNT function, its purpose is to count all the rows in the resulting table, regardless of whether it contains empty, duplicate, or any other values.

If you need to eliminate duplicate values ​​before using the generic function, you should precede the column name in the function definition with keyword DISTINCT. It does not make sense for the MIN and MAX functions, however, using it can affect the execution results. SUM functions and AVG, so you need to consider in advance whether it should be present in each case. In addition, the DISTINCT keyword can be specified at most once in any query.

It is very important to note that summary functions can only be used in the list SELECT clauses and as part of the HAVING clause. In all other cases, this is unacceptable. If the list in the SELECT clause contains summary functions, and the query text does not contain the GROUP BY clause, which ensures the combination of data into groups, then none of the elements of the SELECT clause list can include any references to fields, except for the situation when the fields act as arguments summary functions.

Example 6.4. Determine the first alphabetical product name.

SELECT Min (Item Name) AS Min_Name FROM Item Example 6.4. Determination of the first alphabetical product name.

Example 6.5. Determine the number of transactions.

SELECT Count (*) AS Number_ of trades FROM Trade Example 6.5. Determine the number of transactions.

Example 6.6. Determine the total amount of goods sold.

SELECT Sum (Deal.Quantity) AS Item_Quantity FROM Deal Example 6.6. Determination of the total amount of goods sold.

Example 6.7. Determine the average price of goods sold.

SELECT Avg (Item.Price) AS Avg_Price FROM Item INNER JOIN Deal ON Item.Product Code = Deal.Product Code; Example 6.7. Determination of the average price of goods sold.

SELECT Sum (Item.Price * Deal.Quantity) AS Cost FROM Item INNER JOIN Deal ON Item.Product Code = Deal.Product Code Example 6.8. Calculating the total cost of goods sold.

GROUP BY clause

Queries often require subtotals, which is usually indicated by the appearance of the phrase "for each ..." in the query. The GROUP BY clause is used for this purpose in the SELECT statement. A query that contains a GROUP BY is called a grouping query because it groups the data resulting from a SELECT operation and then creates a single summary row for each individual group. The SQL standard requires the SELECT clause and GROUP BY clause to be closely related. With a GROUP BY clause in a SELECT statement, each list item in the SELECT clause must have a single value for the entire group. Moreover, the SELECT clause can only include the following element types: field names, summary functions, constants, and expressions that include combinations of the above.

All field names listed in the SELECT clause must appear in the GROUP BY clause, unless the column name is used in final function... The reverse is not true — the GROUP BY clause may contain column names that are not listed in the SELECT clause.

If a WHERE clause is used with GROUP BY, then it is processed first, and only those rows that satisfy the search condition are grouped.

The SQL standard specifies that when grouping is performed, all missing values ​​are treated as equal. If two table rows in the same grouping column contain NULL and identical values ​​in all other non-empty grouped columns, they are placed in the same group.

Example 6.9. Calculate the average volume of purchases made by each customer.

SELECT Client.Surname, Avg (Deal.Quantity) AS Average_amount FROM Client INNER JOIN Deal ON Client.Client Code = Deal.Client Code GROUP BY Client.Surname Example 6.9. Calculating the average volume of purchases made by each customer.

The phrase "by each customer" is reflected in the SQL query as a sentence GROUP BY Client. Surname.

Example 6.10. Determine how much each item was sold for.

SELECT Item.Name, Sum (Item.Price * Deal.Quantity) AS Cost FROM Item INNER JOIN Deal ON Item.Product Code = Deal.Product Code GROUP BY Item.Name Example 6.10. Determination of how much the product of each item was sold for.

SELECT Client.Firm, Count (Deal.Trade Code) AS Number_ of_ trades FROM Client INNER JOIN Deal ON Client.Client Code = Deal.Client Code GROUP BY Client.Firm Example 6.11. Counting the number of transactions carried out by each firm.

SELECT Client.Firm, Sum (Deal.Quantity) AS Total_Quantity, Sum (Product.Price * Deal.Quantity) AS Cost FROM Product INNER JOIN (Client INNER JOIN Deal ON Client.Client Code = Deal.Client Code) ON Product.Product Code = Deal .Product Code GROUP BY Client.Firm Example 6.12. Calculation of the total amount of goods purchased for each company and its value.

Example 6.13. Determine the total cost of each product for each month.

SELECT Item.Name, Month (Deal.Date) AS Month, Sum (Item.Price * Deal.Quantity) AS Cost FROM Item INNER JOIN Deal ON Item.Product Code = Deal.Product Code GROUP BY Item.Name, Month (Deal.Date ) Example 6.13. Determination of the total cost of each product for each month.

Example 6.14. Determine the total cost of each product of the first grade for each month.

SELECT Item.Name, Month (Deal.Date) AS Month, Sum (Item.Price * Deal.Quantity) AS Cost FROM Item INNER JOIN Deal ON Item.Product Code = Deal.Product Code WHERE Item.Variety = "First" GROUP BY Item .Name, Month (Deal.Date) Example 6.14. Determination of the total cost of each product of the first grade for each month.

HAVING clause

HAVING reflects all data blocks previously grouped by GROUP BY that satisfy the conditions specified in HAVING. it additional opportunity"filter" the output set.

The conditions in HAVING are different from the conditions in WHERE:

• HAVING excludes aggregated value groups from the result dataset;
• WHERE excludes from the calculation of aggregate values ​​by grouping records that do not meet the condition;
• aggregate functions cannot be specified in the WHERE search clause.

Example 6.15. Identify firms with more than three transactions in total.

SELECT Client.Firm, Count (Deal.Number) AS Number_ of_ trades FROM Client INNER JOIN Deal ON Client.Client Code = Deal.Client Code GROUP BY Client.Firm HAVING Count (Deal.Number)> 3 Example 6.15. Identification of firms with more than three transactions in total.

Example 6.16. Display a list of products sold for more than RUB 10,000.

SELECT Product.Name, Sum (Product.Price * Deal.Quantity) AS Cost FROM Product INNER JOIN Deal ON Product.Product Code = Deal.Product Code GROUP BY Product.Name HAVING Sum (Product.Price * Deal.Quantity)> 10000 Example 6.16. Displaying a list of products sold for more than 10,000 rubles.

Example 6.17. Display a list of products sold for more than 10,000 without specifying the amount.

SELECT Item.Name FROM Item INNER JOIN Deal ON Item.Product Code = Deal.Product Code GROUP BY Item.Name HAVING Sum (Item.Price * Deal.Quantity)> 10000 Example 6.17. Displaying a list of products sold for more than 10,000 without specifying the amount.

Starting from version 4.0, the MySQL DBMS has a rather convenient ability to count the number of all records suitable for a query, when the number of records is limited by LIMIT. When working with a database search, as well as when making selections from tables with big amount records, such functionality is simply necessary.

Syntax. In the SELECT query, the SQL_CALC_FOUND_ROWS option must be specified before the list of columns. Here's the beginning of the syntax for the SELECT clause.

SELECT




select_expr, ... ...

Thus, by doing SELECT query SQL_CALC_FOUND_ROWS The DBMS will count the total number of rows matching the query condition and store this number in memory. Naturally, the SELECT SQL_CALC_FOUND_ROWS query only makes sense when the (LIMIT) constraint is used. Immediately after executing a select query to get the number of records, you need to execute another SELECT query: SELECT FOUND_ROWS () ;. As a result, MySQL will return one row with one field, which will store the number of rows.

An example of the requests themselves:

> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE number> 100 LIMIT 10;
> SELECT FOUND_ROWS ();

The first query will return (print) 10 rows of the tbl_name table for which the condition number> 100 is true. The second call to the SELECT command will return the number of rows that the first SELECT command would have returned if it had been written without a LIMIT clause. Although using the SELECT SQL_CALC_FOUND_ROWS command requires MySQL to recalculate all the rows in the result set, this method is still faster than without LIMIT, since it does not need to send the result to the client.

Sample requests from PHP:

$ result = mysql_query ("SELECT SQL_CALC_FOUND_ROWS * FROM table1 LIMIT 0, 10", $ link);
while ($ row = mysql_fetch_assoc ($ result))
{
var_dump ($ row);
}

$ result = mysql_query ("SELECT FOUND_ROWS ()", $ link);
$ num_rows = mysql_result ($ result, 0);
echo "$ num_rows Rows \ n";

As a result of executing the code, provided that $ link points to an open connection to the DBMS, PHP will output 10 rows from table1, and then an integer value for the number of rows that match the query (excluding LIMIT).

In queries with UNION, SQL_CALC_FOUND_ROWS can behave in two ways due to the fact that LIMIT can appear in several places. Row counting can be done for individual SELECT queries, or for the entire query after the concatenation.

The purpose of SQL_CALC_FOUND_ROWS for UNION is that it should return the number of rows that will be returned without a global LIMIT. The conditions for using SQL_CALC_FOUND_ROWS with UNION are as follows:

• The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT statement.
• FOUND_ROWS () will only be accurate if UNION ALL is used. If UNION is specified without ALL, deduplication occurs and FOUND_ROWS () is only approximate.
• If LIMIT is not present in UNION, then SQL_CALC_FOUND_ROWS is ignored and the number of rows in the temporary table that is created to execute the UNION is returned.

To determine the number of records in MySQL table, you need to use special function COUNT ().

The COUNT () function returns the number of records in a table that match a given criterion.

The COUNT (expr) function always counts only those rows where expr evaluates to NOT NULL.

An exception to this rule is the use of the COUNT () function with an asterisk as an argument - COUNT (*). In this case, all rows are considered, regardless of whether they are NULL or NOT NULL.

For example, the COUNT (*) function returns the total number of records in a table:

SELECT COUNT (*) FROM table_name

How to count the number of entries and display

An example PHP + MySQL code for counting and displaying the total number of rows:

$ res = mysql_query ("SELECT COUNT (*) FROM table_name") $ row = mysql_fetch_row ($ res); $ total = $ row; // total records echo $ total; ?>

This example illustrates the simplest use of the COUNT () function. But with this function, you can perform other tasks as well.

By indicating specific column table as a parameter, the COUNT (column_name) function returns the number of records in this column that do not contain NULL. NULL entries are ignored.

SELECT COUNT (column_name) FROM table_name

You cannot use the mysql_num_rows () function, because in order to find out the total number of records, you need to execute the SELECT * FROM db query, that is, get all the records, which is undesirable, therefore, it is preferable to use the count function.

$ result = mysql_query ("SELECT COUNT (*) as rec FROM db");

Using the COUNT () function by example

Here's another example of using the COUNT () function. Let's say you have an ice_cream table with an ice cream catalog that contains category IDs and ice cream names.

SQL Lesson 8. Grouping Records and the COUNT () Function

Let's remember what messages and in what topics we have. To do this, you can use the usual query:

But what if we just need to find out how many messages there are on the forum. To do this, you can use the built-in function COUNT ()... This function counts the number of rows. Moreover, if * acts as an argument to this function, then all the rows of the table are counted. And if a column name is specified as an argument, then only those rows that have a value in the specified column are counted.

In our example, both arguments will give the same result, since all columns in the table are NOT NULL. Let's write a query using the id_topic column as an argument:

SELECT COUNT (id_topic) FROM posts;

So, there are 4 posts in our threads. But what if we want to know how many posts each topic has. To do this, we need to group our messages by topic and calculate the number of messages for each group. For grouping in SQL, use the operator GROUP BY... Our request will now look like this:

SELECT id_topic, COUNT (id_topic) FROM posts GROUP BY id_topic;

Operator GROUP BY tells the DBMS to group data by the id_topic column (i.e., each topic is a separate group) and for each group to count the number of rows:

Well, in the topic with id = 1 we have 3 messages, and with id = 4 - one. By the way, if there were no values ​​in the id_topic field, then such rows would be combined into a separate group with a NULL value.

Suppose we are only interested in groups with more than two messages. In a normal query, we would specify a condition using the operator WHERE, but this operator can work only with strings, and for groups the same functions are performed by the operator HAVING:

SELECT id_topic, COUNT (id_topic) FROM posts GROUP BY id_topic HAVING COUNT (id_topic)> 2;

As a result, we have:

In lesson 4, we considered what conditions can be set by the operator WHERE, the same conditions can be set by the operator HAVING, you just need to remember that WHERE filters the lines, and HAVING- groups.

So today we learned how to create groups and how to count the number of rows in a table and in groups. Generally, together with the operator GROUP BY you can use other built-in functions, but we will study them later.

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. Aggregate SQL functions act on column values ​​in order to obtain 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 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).

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:

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.

You want to know the minimum wages employees of department number 42. To do this, write the following request:

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 request:

The request will return the value 18352.80

The time has come 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 work experience of employees of department number 42. To do this, write the following query:

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:

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:

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 .

Example 12. There is an ad 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), while 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
Real estate	Apartments	89	18690
Real estate	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
Real estate	Houses	47	9870
Leisure	Music	117	7605
Leisure	Games	41	2665

Using SQL statement GROUP BY, find the amount of money earned from submitting 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.

Use the SQL GROUP BY statement to find out which part of each category had the most ads. 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 ​​in one table can be obtained combining query results using the UNION operator .

Relational databases data and SQL language