I have a request like:

SELECT i.*, COUNT(*) AS currencies, SUM(ig.quantity) AS total, SUM(g.price * ig.quantity) AS price, c.briefly AS cname FROM invoice AS i, invoice_goods AS ig, good g LEFT JOIN currency c ON (c.id = g.currency) WHERE ig.invoice_id = i.id AND g.id = ig.good_id GROUP BY g.currency ORDER BY i.date DESC;

those. a list of orders is selected, in which the total costs of goods in different currencies are calculated (the currency is set for the product, the cname column in the result is the name of the currency)

you need to get the number of records with the same i.id in the currencies result column, however, experiments with the COUNT() parameters did not lead to anything - it always returns 1

Question: Is it possible to get the true value in the currencies column? Those. if goods are ordered with prices in 3 different currencies, currencies=3 ?

However, MySQL takes too many liberties with respect to SQL. For example, what does i.* mean in the context of this select? All columns of the invoice table? Since no group function applies to them, it would be nice if they were listed in GROUP BY, otherwise the principle of grouping rows is not entirely clear. If you need to receive all goods for all orders by currency, this is one thing, if you need to receive all goods grouped by currency for each order, this is completely different.
Based on your select, we can assume the following data structure:
Invoice table:

Invoice_goods table:

Goods table:

Currency table:

What will your current select return? In theory, it will return N-rows for each order for each currency in which this order contains goods. But due to the fact that nothing other than g.currency is specified in group by, this is not obvious :), moreover, the c.briefly column also contributes to the implicit formation of groups. What we have as a result, for each unique combination of i.*, g.currency and c.briefly, a group will be formed to the lines of which the SUM and COUNT functions will be applied. The fact that as a result of playing with the COUNT parameter you always got 1 means that there was only one record in the resulting group (i.e. the groups are not formed as you may require, can you describe the requirements in more detail?). It is not clear from your question what you would like to know - how many different currencies were involved in the order or how many orders were in a given currency? In the first case, several options are possible, it all depends on the capabilities of MySQL; in the second, you need to write the select expression differently.

However, MySQL takes too many liberties with respect to SQL. For example, what does i.* mean in the context of this select? All columns of the invoice table?

Yes exactly. But this does not play a big role, because... There are no useful columns among them in this case. Let i.* be i.id . To be specific.

What will your current select return? In theory, it will return N-rows for each order for each currency in which this order contains goods. But due to the fact that group by does not specify anything other than g.currency, this is not obvious :),

Exactly.
It will return the following (in this example, from i I select only id , and not all columns):

id	currencies	total	price	cname
33	1	1.00	198.00	B.F.
33	1	4.00	1548.04	RUB

Moreover, the c.briefly column also contributes to the implicit formation of groups.

How? Tables are joined by c.id=g.currency, and grouped by g.currency .

The fact that as a result of playing with the COUNT parameter you always got 1 means that there was only one record in the resulting group

No, the group was built from 1st records. As far as I understand this, COUNT() returns 1 for this reason (after all, the columns that are different in the group (though, except for the currency column) are created by aggregate functions).

(i.e. the groups are not formed as you may require, can you describe the requirements in more detail?).

Groups are formed as needed, each group -- This total cost of goods in each currency. However, besides that, I need to calculate how much same elements in this group.

It is not clear from your question what you would like to know - how many different currencies were involved in the order or how many orders were in a given currency?

Yeah, I made a little money. Just the first one.

dmig[dossier]
By "implicit" participation in the formation of a group, I mean that if the column is not specified in the GROUP BY, and at the same time is NOT an argument to the group function, then the result of the select will be identical to what it would be if that column WAS specified in the GROUP BY. Your select and the select below will produce exactly the same result (don’t pay attention to the joins, I just brought them to a single recording format):

Select i.id id, count(*) currencies, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id = i. id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) group by i.id, c.briefly

It turns out that in each row of the resulting sample there is one, and only one currency (if it were different, then there would be two rows). What number of elements are we talking about in this case? About order items? Then your selection is absolutely correct, just for this currency, there is only one item in this order.
Let's look at the data schema:

1. There are many items (lines) in one order, right?
2. Each item is a product in the goods directory, right?
3. Each product has a specific (and only one) currency, this follows from c.id = g.currency, right?

How many currencies are in the order? There are as many points in it with DIFFERENT currencies.
Adding g.price * ig.quantity makes sense only for points in one currency;) (although kilometers with hours can also be added :) So what doesn’t suit you!? You state that you need how many different currencies were involved in the order
and in this case, doing this within the framework of the same select without all sorts of tricks (which MySQL most likely will not do) will not work;(
Unfortunately, I'm not a MySQL expert. In Oracle you can do this with one select, but will this advice help you? Hardly;)

# There are many items (lines) in one order, right?
# Each item is a product in the goods directory, right?
# Each product has a specific (and only one) currency, this follows from c.id = g.currency, right?

So.
One order: one record in the invoice table, it corresponds to n(>0) records in invoice_goods, each of which corresponds to 1 record in the goods table, the “currency” record in each of which, in turn, corresponds to the 1st record in the currency table ( LEFT JOIN - in case of editing the currency directory with crooked hands - tables like MyISAM do not support foreign keys).

How many currencies are in the order? There are as many points in it with DIFFERENT currencies.

Yes exactly.

Adding g.price * ig.quantity makes sense only for points in one currency;) (although kilometers with hours can also be added :)

This is why grouping is done by currency id (g.currency).

In Oracle you can do this with one select, but will this advice help you?

M.b.
I talked a little with Oracle and am familiar with pl/sql.

Option #1.

Select a.*, count(*) over (partition by a.id) currencies from (select i.id id, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) group by i.id, c.briefly) a

This uses the so-called analytical function. With 99% probability it does NOT work in MySQL.

Option #2.
A function is created, countCurrencies for example, which, based on the order id, returns the number of currencies that participated in it and then:

Select i.id id, countCurrencies(i.id) currencies, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) group by i.id, c.briefly, countCurrencies(i.id)

It may work... but it will be called for each currency of each order. I don’t know if MySQL allows you to do GROUP BY by function...

Option #3

Select i.id id, agr.cnt currencies, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id ) join good go on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) left outer join (select ii.id, count(distinct gg.currency) cnt from invoice ii, invoce_goods iig, good gg where ii.id = iig.invoice_id and gg.id = iig.good_id group by ii.id) agr on (i.id = agr.id) group by i.id, c.briefly, agr. cnt

Probably the most correct... and quite possibly the most working option of all.

The fastest is Option No. 1. No. 2 is the most ineffective, because The more currencies in the order, the more often they are counted.
No. 3 is also, in principle, not the best in terms of speed, but at least you can rely on caching inside the DBMS.

The result of all three selects will be the following:

id		currencies		total		price		cname
33		2		1.00		198.00		B.F.
33		2		4.00		1548.04		RUB

for the same id the number in the currencies column will always be the same, is this what you need?

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

Building calculated fields

In general, to create calculated (derived) field the SELECT list must contain some SQL expression. These expressions use the arithmetic operations of addition, subtraction, multiplication, and division, as well as 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 the table or query that is listed in the FROM clause list of the corresponding statement. When constructing complex expressions, parentheses may be needed.

SQL standards allow you to explicitly specify the names of the columns of the resulting table, for which the AS clause is used.

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

Example 6.2. Get a list of companies indicating the surnames and initials of clients.

SELECT Company, Last Name+""+ Left(First Name,1)+"."+Left(Middle Name,1)+"."AS Full Name FROM Client Example 6.2. Obtaining a list of companies indicating the last name and initials of clients.

The request uses the built-in Left function, 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 indicating the year and month of sale.

SELECT Product.Name, Year(Transaction.Date) AS Year, Month(Transaction.Date) AS Month FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode 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 final (aggregate) functions within the SQL query, you can obtain a number of general statistical information about the set of selected values ​​of the output set.

The user has access to the following basic final 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 request field;
• Avg (Expression) - this function allows you to calculate the average of a set of values ​​​​stored in a specific field of records selected by a query. It is an arithmetic average, i.e. the sum of values ​​divided by their number.
• Sum (Expression) - Calculates the sum of the set of values ​​​​contained in a specific field of the records selected by the query.

Most often, column names are 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 on 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 for numeric fields, with the exception of COUNT(*) . When calculating the results of any function, all null values ​​are first eliminated, and then the required operation is applied only to the remaining specific column values. The COUNT(*) option is a special use case of the COUNT function; its purpose is to count all rows in the resulting table, regardless of whether it contains nulls, duplicates, or any other values.

If you need to eliminate duplicate values ​​before using a generic function, you must precede the column name in the function definition with the DISTINCT keyword. It has no meaning for the MIN and MAX functions, but its use can affect the results of the SUM and AVG functions, so you need to consider whether it should be present in each case. In addition, the DISTINCT keyword can only be specified once in any query.

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

Example 6.4. Determine the first alphabetical name of the product.

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

Example 6.5. Determine the number of transactions.

SELECT Count(*) AS Number_of_deals FROM Deal Example 6.5. Determine the number of transactions.

Example 6.6. Determine the total quantity of goods sold.

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

Example 6.7. Determine the average price of goods sold.

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

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

GROUP BY clause

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

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

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

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

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

SELECT Client.LastName, Avg(Transaction.Quantity) AS Average_Quantity FROM Client INNER JOIN Trade ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.LastName Example 6.9. Calculate the average volume of purchases made by each customer.

The phrase “every customer” is reflected in the SQL query in the form of a sentence GROUP BY Client.LastName.

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

SELECT Product.Name, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name Example 6.10. Determination of the amount for which each product was sold.

SELECT Client.Company, Count(Transaction.TransactionCode) AS Number_of_transactions FROM Client INNER JOIN Transaction ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.Company Example 6.11. Counting the number of transactions carried out by each firm.

SELECT Customer.Company, Sum(Transaction.Quantity) AS Total_Quantity, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN (Customer INNER JOIN Transaction ON Customer.ClientCode=Transaction.CustomerCode) ON Product.ProductCode=Transaction .Product Code GROUP BY Client.Company Example 6.12. Calculation of the total quantity of goods purchased for each company and its cost.

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

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

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

SELECT Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode WHERE Product.Grade="First" GROUP BY Product .Name, Month(Transaction.Date) Example 6.14. Determination of the total cost of each first-class product for each month.

HAVING offer

Using HAVING, all data blocks previously grouped using GROUP BY that satisfy the conditions specified in HAVING are reflected. This is an additional option to "filter" the output set.

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

• HAVING excludes groups with aggregated value results from the resulting data set;
• WHERE excludes records that do not satisfy the condition from the calculation of aggregate values ​​by grouping;
• Aggregate functions cannot be specified in the WHERE search condition.

Example 6.15. Identify companies whose total number of transactions exceeded three.

SELECT Client.Company, Count(Trade.Quantity) AS Number_of_deals FROM Client INNER JOIN Trade ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.Company HAVING Count(Transaction.Quantity)>3 Example 6.15. Identification of firms whose total number of transactions exceeded three.

Example 6.16. Display a list of goods sold for more than 10,000 rubles.

SELECT Product.Name, Sum(Product.Price*Deal.Quantity) AS Cost FROM Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name HAVING Sum(Product.Price*Deal.Quantity)>10000 Example 6.16. Displaying a list of goods 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 Product.Name FROM Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode GROUP BY Product.Name HAVING Sum(Product.Price*Transaction.Quantity)>10000 Example 6.17. Display a list of products sold for more than 10,000 without specifying the amount.

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

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

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 for which the result of expr is NOT NULL .

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

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

SELECT COUNT(*) FROM table_name

How to count the number of records and display them on the screen

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 you can also perform other tasks using this function.

By specifying a specific table column as a parameter, the COUNT(column_name) function returns the number of records in that column that do not contain a NULL value. Records with NULL values ​​are ignored.

SELECT COUNT(column_name) FROM table_name

The mysql_num_rows() function cannot be used because in order to find out the total number of records, you need to run a SELECT * FROM db query, that is, get all the records, and this is not desirable, so it is preferable to use the count function.

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

Using the COUNT() function as an example

Here is another example of using the COUNT() function. Let's say there is a table ice_cream with an ice cream catalog, which contains category identifiers and ice cream names.

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.