Vchimosya pіdbivati ​​pіdbags. Hі, not the results of the SQL calculation, but the subbags of the values ​​of the data base tables. Aggregate SQL functions range by value up to a single result value. The SQL aggregate functions SUM, MIN, MAX, AVG and COUNT are most often used. There are two differences between the aggregation functions. First: aggregate functions are victorious by powerful forces and turn one resultant value. Other: Aggregate functions are matched with the SQL GROUP BY operator, that is, grouping by fields (stands) to subtract the resulting values ​​from the skin group. Let's take a look at the beginning of the breakdown of aggregate functions without grouping.

SQL SUM function

The SQL SUM function rotates the sum of the spanning values ​​of a database table. Vaughn can only zastosovuvatisya until stovptsіv, the meaning of such a number. Asking SQL to retrieve the resulting sumi starts like this:

SELECT SUM (IM'I_COLUMN) ...

After that, follow FROM (IM'YA_TABLES), and then after an additional WHERE construct, you can also specify the mind. In addition, DISTINCT can be written before the name of the column, which means that only unique values ​​are protected. For locking, all values ​​are refunded (for which you can especially specify not DISTINCT, but ALL, but the word ALL is not binding).

If you want to download to the database of this lesson on MS SQL Server, if the DBMS is not installed on your computer, then you can install it by following the instructions for this request .

We work with the data base of companies - Company1. Script for creating qієї dannyh database, її table and burnt table danimi - at the file for cim sent .

example 1.Є data base of firms with data about її pіdrozdіli and svіrobіtnikіv. The Staff table also contains data on the salary of recruiters. The selection from the table may look like this (to enlarge the picture, click on it with the left mouse button):

To fix the sum of the sums of the surplus payrolls, it is possible to advance the request (on MS SQL Server - from the front design of USE company1;):

SELECT SUM (Salary) FROM Staff

Tsey asks for the turn of the value 287664.63.

And now . At the right, it’s already beginning to make things easier, bringing them closer to quiet, which are practical.

SQL MIN Function

The SQL MIN function also uses a number of values ​​to rotate the minimum average value of the value. This function has a syntax similar to that of the SUM function.

Example 3. The data base is the table - the same as in the application 1.

It is necessary to find out about the minimum wage for the spіvrobіtnikіv vіddіl z number 42. For whom we write the advance request (on MS SQL Server - with the front design USE company1;):

The return value is 10,505.90.

I renew right for self-sufficiency. For those who have the right to do so, it is necessary not only the Staff table, but also the Org table, to avenge data about the company's updates:

Example 4. The table Org is added to the Staff table, which avenges the data of the company's updates. Vivesti minimum number of rokiv, opratsovanyh one spіvrobіtnik vіddіlі, rastashovanuyu near Bostonі.

SQL MAX function

The SQL MAX function works in a similar way, so it will stop if it is necessary to set the maximum value of the average value of the system.

Example 5.

It is necessary to find out about the maximum salary of the spivrobitnik in the case number 42. For which we write the advance request (on MS SQL Server - with the front design USE company1;):

Read the value 18352.80

come hour right for self-sufficiency.

Example 6. I am familiar with two tables - Staff and Org. Introduce the name of the maximum value of the commission, as it will take one spin from the leader, which belongs to the group of payments (Division) Eastern. Vikoristati JOIN (creating tables) .

SQL AVG function

Referred to the syntax for forward function descriptions as correct and similar to SQL AVG functions. This function rotates the average value of the average value of the spat.

Example 7. The data base is that table - the same as in the front butts.

Let's find out about the average work experience of the spivrobitniks of the worker with number 42. For whom we write an offensive request (on MS SQL Server - with the front design of USE company1;):

The result will be a value of 6.33

Example 8. Pratsiyuemo with one table - Staff. Enter the average salary of practitioners with experience from 4 to 6 years.

SQL COUNT function

The SQL COUNT function rotates the number of records in a database table. If you select SELECT COUNT(IM_COLUMN) ... in the query, the result will be the number of records without uranium records, which have NULL (undefined) values. If you win as an argument with a z and start asking SELECT COUNT(*) ..., then the result will be the number of all records (rows) in the table.

Example 9. The data base is that table - the same as in the front butts.

It is necessary to find out about the number of all spivrobitniks, how to deduct commissions. The number of columns, for which Comm values ​​are not NULL, in turn, it is more forward (on MS SQL Server - with the front construction USE company1;):

SELECT COUNT (Comm) FROM Staff

The result will be the value 11.

butt 10 The data base is that table - the same as in the front butts.

If it is necessary to recognize the total number of records in the table, then it will be automatically requested as an argument of the COUNT function (on MS SQL Server - with the front construction USE company1;):

SELECT COUNT (*) FROM Staff

The result will be the value 17.

In the offensive right for independent vision it is necessary to vikoristovuvat pidzapit.

Example 11. Pratsiyuemo with one table - Staff. Vivesti kіlkіst pratsіvnіv і vіddіlі planuvannya (Plains).

Aggregate functions at once from SQL GROUP BY (grouping)

Now let's look at aggregate functions at once with the SQL GROUP BY operator. The SQL GROUP BY operator is used to group the resulting values ​​of columns in database tables. On site lesson, assignments to which operator .

Pratsyuvatimemo with data base "Portal goloshen 1". Script to create a database of data, a table and a burning table of data - at the file for cim requests .

butt 12 Also, the data base of the portal is stunned. Vaughn to avenge the Ads table, as to avenge the data about the slander, filing for the day. The Category section is to avenge data about the great categories of slander (for example, Nerukhomist), and the Parts section is about other parts that can be included up to the category (for example, parts of Apartments and Dachas are parts of the Nerukhomist category). The Units clause is to collect data about the number of voucher filings, and the Money stanza is about pennies sums that are taken away for filing a slander.

Vykoristovuyuchi operator SQL GROUP BY, know the sum of pennies, otrimanih for submitting a slander in the skin category. We write an offensive request (on MS SQL Server - from the front construction USE adportal1;):

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

butt 13 The data base is the table itself, which is in the front butt.

Wicked operator SQL GROUP BY, z'yasuvati, in this part of the skin category, the largest number of vomits were filed. We write an offensive request (on MS SQL Server - from the front construction USE adportal1;):

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

The result will be a table:

Subsumkovy and individual values ​​in one table can be taken to combine the results of requests for the help of the UNION operator .

Relational databases and language SQL

Pobudova counted fields

At the top of the vapadka for folding counted (pokhіdny) field in the SELECT list, specify the correct version of the SQL move. At tsikh vyslovlyuvannyah arithmetic operations of adding, vidnimannya, multiplying that podіlu, and also vbudovany functions mov SQL. You can specify the name of any table (field) of the table for the request, but you can also choose the name of the table for the request, as shown in the list of propositions FROM for the instructions. When prompted folding virazіv may need bows.

The SQL standards allow you to explicitly specify the names of the columns in the resulting table, most notably the AS clause.

SELECT Commodity.Name, Commodity.Price, Favor.Quantity, Commodity.Price*Favor.Quantity AS Variant FROM Commodity INNER JOIN Favor ON Commodity. Example 6.1. Rozrahunok zagalnoy vartosti kozhny ї grounds.

Example 6.2. Select the list of companies from the designated names and initial clients.

SELECT Company, Name+""+ Left(First Name,1)+"."+Left(By Father,1)+"."AS ПІБ FROM Client Example 6.2. Adding to the list of firms from the designated names and initials of clients.

At the request of the vikoristan, the Left function is introduced, which allows one symbol of evil to be displayed in the textual change in a given mode.

Example 6.3. Check the list of goods from the date and month of sale.

SELECT Item.Name, Year(Property.Date) AS Year, Month(Property.Date) AS Month FROM Item INNER JOIN Item ON Item.ItemID=Year.ProductID Example 6.3. Otrimanya to the list of goods from the appointed date and month of sale.

At the request of the vikoristan, the Year and Month functions are used to see the date and month of the date.

Wicker list of bag functions

For help subbag (aggregate) functions within the framework of SQL query, you can take a number of specific statistical data about the multiplier of the selected values ​​of the output set.

Koristuvachevy available and basic subbag functions:

• Count (Viraz) - displays the number of records in the output set of SQL query;
• Min/Max (Viraz) - assign the least and the most impersonal value to the current field;
• Avg (viraz) - this function allows you to decompose the mean value of the anonymous values ​​that are saved in the first field of selected records. Vono є arithmetic mean values, tobto. the sum of the value, the subdivision of their quantity.
• Sum (Viraz) - calculates the sum of the anonymous values, which are the same as the number of entries selected for the first field.

Most often, the names of the saints come forward like a viraz. Viraz can be counted according to the table values.

All these functions operate with values ​​in a single column of the table or with an arithmetic view and rotate a single value. The functions COUNT , MIN and MAX stop both before numeric fields, and also before non-numeric fields, while the functions SUM and AVG can only win in different numeric fields, with a little COUNT(*) . When calculating the results of any functions, all empty values ​​are included in the list, if the operation needs to be stopped only to specific values, which are left out. The COUNT(*) option is a special kind of variation of the COUNT function, which is recognized by all rows in the resulting table, independently of each other, to be empty there, duplicating whether or not other values.

If you need to include duplicate values ​​before deleting a specific function, then put the keyword DISTINCT before the names of a specific function. It may not be sensible for the MIN and MAX functions, the protein can be interfering with the results of the SUM and AVG functions, and this needs to be further subdued, as it may be present in a specific skin condition. In addition, the keyword DISTINCT can only be specified in any request no more than once.

It is even more important to note that subbag functions You can win more for the SELECT proposition list than for the HAVING proposition warehouse. In other situations, it is unacceptable. Like the list of the SELECT proposition subbag functions, and the text will include the daily phrase GROUP BY , which ensures the grouping of these groups, then it is impossible to include any elements in the list of propositions SELECT bag functions.

Example 6.4. Designate the alphabet for the name of the product.

SELECT Min(Trade name) AS Min_Name FROM Product Example 6.4. Name the goods after the alphabet.

Example 6.5. Designate the number of favors.

SELECT Count(*) AS Number of lands FROM Count Example 6.5. Designate the number of favors.

Example 6.6. Calculate the sumarnu kіlkіst of the sold goods.

SELECT Sum(Place. Quantity) AS Quantity_product FROM Pair Example 6.6. Appointment of the sumarnoї kіlkostі of the goods sold.

Example 6.7. Calculate the average price of the product sold.

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

SELECT Sum(Item.Price*Perfect.Quantity) AS Variety FROM Item INNER JOIN Item ON Item.ItemId=Perfect.ItemId Example 6.8. Pіdrakhunok vartosti sold goods.

Proposition GROUP BY

Often at the request it is necessary to form the intermediate pouches, which sound like the phrase "skin ...". For which, the proposition GROUP BY wins in the SELECT statement. The request, in the case of GROUP BY, is called a group request, the shards are grouped with data, taken away as a result of the SELECT operation, after which the skin group is formed into a single total row. The SQL standard suggests that the SELECT clause and the GROUP BY clause should be closely related to each other. For the presence in the SELECT statement of the GROUP BY clause, the skin element of the list in the SELECT clause is responsible for the single value for the entire group. Moreover, the SELECT proposition can only include the following types of elements: field names, subbag functions, a virazi constant that includes combinations of overridden elements

All field names put in the SELECT proposition list must be present in the GROUP BY phrase - for a wink if they are victorious in sub-bag function. The reverse rule is not fair - the GROUP BY phrase can have column names, which are the same as the SELECT proposition list.

As always, GROUP BY wins the proposition WHERE , it turns into the first one, and the grouping follows these rows, as if they please the mind with a joke.

The SQL standard stipulates that after grouping all daily values ​​are considered equal. If two rows of tables in the same grouped column contain NULL values ​​and identical values ​​in all other non-empty grouping columns, they are placed in the same group.

Example 6.9. Calculate the average total of purchases made by a leather buyer.

SELECT Client.Privacy, Avg(Area.Amount) AS Average_Amount FROM Client INNER JOIN Client ON Client.ClientCode=Avg.ClientId GROUP BY Client.About Example 6.9. Calculation of the average obligation of purchases made by a skin buyer.

The phrase "skin buyer" found its expression in SQL-application in looking at propositions GROUP BY Client.Prizvische.

Example 6.10. Significantly, yak sumu bulo sold the goods of the skin name.

SELECT Item.Name, Sum(Item.Price*Product.Quantity) AS Variety FROM Item INNER JOIN Item ON Item.ItemID=Product.ItemID GROUP BY Item.Name Example 6.10. Appointment, yak sumu Bulo sold the goods of the leather name.

SELECT Client.Company, Count(Party.PartyCode) AS Count_Parts FROM Client INNER JOIN Privilege ON Client.ClientCode=Party.ClientID GROUP BY Client.Company Example 6.11. Pіdrahunok kіlkostі ugod, zdіysnenih skin firm.

SELECT Customer.Company, Sum(Customer.Quantity) AS Global_Quality, Sum(Item.Price*City.Quantity) AS Variant FROM Commodity INNER JOIN (Customer INNER JOIN Customer ON Customer.CustomerCode=Access.CustomerCode.Code) G. Firm Example 6.12. Pіdrahunok zagalnoї kіlkostі purchased leather ї fіrmi goods ta її vartostі.

Example 6.13. Calculate the sumarnu vartіst of a leather product for a month.

SELECT Item.Name, Month(Property.Date) AS Month, Sum(Product.Price*Property.Quantity) AS Variety FROM Item INNER JOIN Item ON Item.ProductID=Property.ProductID GROUP BY Item.Name, Month(Property.Date) ) ) Example 6.13. The appointment of the sumarnoї vartostі of the leather goods is leather mіsyats.

Example 6.14. Appreciate the sumarnu vartіst of the leather goods of the first ґatunka for the leather mіsyats.

SELECT Item.Name, Month(Product.Date) AS Month, Sum(Product.Price*Property. Quantity) AS Variety FROM Item INNER JOIN Item ON Item. . Name, Month (Pleasure. Date) Example 6.14. The appointment of the sumarnoї vartostі of the leather goods of the first ґatunku for the leather mіsyats.

Proposition HAVING

For the help of HAVING, all groups are forwarded for the help of GROUP BY blocks of data, which pleases the minds of HAVING. Tse dodatkova mozhlivist "profiltruvati" vihіdniy nabіr.

WASHING HAVING Vіdrіznyayutsya vіd zіd WHERE :

• HAVING excludes from the result set of data groups from the results of aggregation values;
• WHERE include aggregation of aggregate values ​​for grouping records that do not satisfy the mind;
• For the mind of the joke WHERE you can not set aggregate functions.

Example 6.15. Designate firms, in some of them, the number of favors has outweighed three.

SELECT Client.Company, Count(Area.Quantity) AS Number of_Areas FROM Customer INNER JOIN Facility ON Customer.CustomerCode=Accommodation.CustomerCode GROUP BY Customer.Company HAVING Count(Access.Amount)>3 Example 6.15. The appointment of firms, yakі mayut zagalnu kіlkіst favors outweighed three.

Example 6.16. Enter a list of goods sold for a sum of over 10,000 kr.

SELECT Product.Name, Sum(Item.Price*Access.Quality) AS Varity FROM Product INNER JOIN Amenity ON Product.Item.Code=Access.ItemId GROUP BY Product.Name HAVING Sum(Item.Price*Access.Quality)>1000 Example 6.16. Visnovok transfer of goods sold at the sum of over 10,000 krb.

Example 6.17. Enter a list of goods sold at a retail price of over 10,000 without a sum.

SELECT Product.Name FROM Product INNER JOIN Favor ON Product.ProductCode=Product.ProductID GROUP BY Product.Name HAVING Sum(Product.Price*Favor.Quantity)>10000 Example 6.17. Viewing the list of goods sold for sums over 10,000 without a sum.

To your chief assistant you know how to win SUM function in SQL Server (Transact-SQL) with syntax and applications.

Description

In SQL Server (Transact-SQL) SUM function turn the total meaning of the virazu.

Syntax

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

ABO syntax of the SUM function when grouping the results for one or more decals of the steps:

Parameters chi arguments

expression1 , expression2 , … expression_n are not included before the SUM function, but they are included before the GROUP BY operator in the end of the SQL proposition.
aggregate_expression - ce stovpets or viraz, which will be summed up.
tables - tables, from which it is necessary to take records. At least one table is to blame, it was recaptured in the FROM statement.
WHERE conditions - non-viscous. Tse wash, how to follow for selected records.

Zastosuvannya

The SUM function may be featured in future versions of 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

Example from one field

Let's take a look at how to apply the SQL Server SUM function to understand how to use the SUM function in SQL Server (Transact-SQL).

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

For this application of the SUM function and the SUM (quantity) virusase, the pseudonym “Total Quantity” was inserted. When the result set is rotated, “Total Quantity” will appear as the field name.

Buttstock DISTINCT

You can twist the DISTINCT statement of the SUM function. For example, hovering lower the SQL statement turns the total salary amount with the unique salary values, making the salary lower than $29,000 per rec.

Yakby two salary became 24,000 dollars. On the other hand, the SUM functions won only one of these values.

Butt of vikoristanny formula

Viraz, which is located near the SUM function, cannot be bound by one field. You can also tweak the formula. For example, you can settle a joint commission.

Transact SQL

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

Vico stock buttstock GROUP BY

In some cases, it is necessary to win the GROUP BY operator with the SUM function.

The SUM function in SQL-movie, regardless of simplicity, is often tricky when working with a database. Z її to help you manually take some intermediate or intermediate results, without going through the help of additional DBMS tools.

Function syntax

For most SQL, the syntax of sum is the same - as an argument, only the names of the fields are victorious, or the deac is arithmetic for a number of them, for which it is necessary to carry out summing.

In vinyatkovyh vipadkah it is possible to convey a specific meaning in the sight of the number of change, prote similar "schemes" are practically not vikoristovuyutsya, to that they do not carry great value in themselves. Below is the syntax of the function on my SQL:

sum (a) - here as a parameter

Varto indicate that keywords can be inserted before the parameter, for example DISTINCT or ALL, so only unique or all values ​​will be taken.

SUM hack butt in SQL

For a residual understanding of the principle of work and function, we should look at a sprinkling of applications. SQL SUM can be reversed as the result is rotated, so in the role of an intermediate value, for example, to recheck the mind.

For the first time, we can look at the option, if it is necessary to turn the amount of sales on the leather product, vrahovyuchi, so the number of purchases can be multiplied. To otrimannya the result is enough vikonati offensive request:

SELECT Item, sum(Purchase Amount) FROM Sales GroupBy Item;

Depending on the team, there will be a unique list of goods with a sum of purchases for skin products.

For another butt, it is necessary to take a list of goods, the amount of sales for which has exceeded the deak value, for example, 100. You can take the result for this task in dek_lcoma in the ways that are the most optimal of all of them:

SELECT Product FROM (SELECT Product, sum(BuyAmount) as Sum FROM Sales) WHERE Sum > 100.

How do you recognize the number of PC models that are released by your partner? How to calculate the average value on a computer, how can you find the same technical characteristics? On the basis of the number and other nutrition related to the actual statistical information, you can take advice for help subbag (aggregate) functions. The standard transfers the following aggregate functions:

All functions rotate the same value. With which function COUNT, MINі MAX zastosovnі before any type of data, at that hour SUMі AVG vikoristovuyutsya less for numerical fields. Residuality between functions COUNT(*)і COUNT(<имя поля>) I believe that a friend behind a fagot does not give up NULL-value.

butt. Find out the minimum and maximum price for a personal computer:

butt. Find out the actual number of computers issued by scribe A:

butt. How can we tell the number of different models, which are listed by the A-type, can be formulated as an offensive rank (according to this fact, the skin model is recorded in the Product table once):

butt. Find out the number of current different models that are allowed by the selection tool A. The number is similar to the one in the front, in which case it is necessary to indicate the total number of models that are allowed by the selection tool A. It is also necessary to know the number of different models in the PC table (they are available for sale).

In order for the selection of statistical indicators to win only unique values, aggregate function arguments you can vicorist DISTINCT parameter. Second parameter ALL vikoristovuєtsya for umovchannyam and conveying the fools of all the meanings that turn in the column. Operator,

How do we need to take into account the number of PC models, viroblenih skin virobnik, then it is necessary to vikoristovuvat proposition GROUP BY, syntactically offensive propositions WHERE.

Proposition GROUP BY

Yakby at SELECT literal stovpets z date, it would be possible to count the number of indications of a specific skin date. For which it is necessary to add the date as a grouping, and even aggregate functions were calculated for the skin combination value (model-date).

Іsnuє sprat of singers Rules for victoring aggregate functions:

• As a result not taken the same row(or one row for each group), then the output data for the calculation, whether it be from the aggregate functions per day. 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 you can't define aggregate functions yourself(function within function). Tobto. in one drink it is not possible, let's say, to achieve a maximum of average values.
• Result of typing function COUNT є whole number(INTEGER) Other aggregate functions decrease the type of data that are being processed.
• Likewise, with the SUM function, the result is subtracted, which shifts the maximum value of the data type, which wins, blames pardon.

Father, I ask you not to take revenge propositions GROUP BY, then aggregate functions, included in SELECT proposition; How to ask for revenge proposition GROUP BY, skin typing rows, which may be the same value of a column or a group of columns, tasks for propositions GROUP BY, become a group, that aggregate functions vykonuyutsya skin group okremo.

Proposition HAVING

Yakscho WHERE clause defines a predicate for filtering rows, then proposition HAVING stagnate after grouping for a designation similar to a predicate that filters groups for values aggregate functions. This proposition is necessary for the re-verification of the meaning, as it is taken away for help aggregate function not a few rows of records, signed in words FROM, a a group of such rows. Therefore, such a re-verification cannot be WHERE.