SQL - Lesson 11

• AVG() The function rotates the average.


• COUNT() The function rotates the number of rows at the column.


• MAX() The function rotates the largest value at the column.


• MIN() The function rotates the smallest value at the column.


• SUM() The function rotates the sum value of the span.

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

Now, we want to know, how much money was brought to us by the postal worker of "Budynok druk" (id=2). Folding such a request is not so easy. Let's calm down, how to fold yoga:

1. From the table of Deliveries (incoming), select the identifiers (id_incoming) of the deliveries, as requested by the "Budynok druku" postal leader (id=2):

2. Now, from the tables in the Journal of deliveries (magazine_incoming), you need to select the goods (id_product) and the quantities that were found in item 1 of the deliveries. Tobto will be requested from paragraph 1 of the deposit:

3. Now we need to add to the resulting table the prices for the products found, which are saved in the prices tables. That's why we need to combine tables Magazine of deliveries (magazine_incoming) and Prices (prices) by item id_product:

4. At the table, which has appeared, the Suma is clearly not hanging, tobto calculated amount. The ability to create such statements has been forwarded to MySQL. For whom it is less necessary to indicate in the inquiry what is to be counted, and what is guilty of counting. Our butt has such a stovpets called summa, and counting the wines will be twir stovptsіv quantity and price. The name of the new building is endorsed by the word AS:

SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id=com

5. Good, we were left with less summa and we know, for the sum of money, the postal worker of "Budinok to Druk" brought us goods. The syntax for using the SUM() function is:

SELECT SUM(table_name) FROM table_name;

Im'ya stovptsya to us v_domo - summa, and the axis of the name of the table we do not have, so that won't be the result of the request. What is work? For such behaviors, MySQL has a Statement. Giving - tse ask for a vote, which one hopes for a unique name and which one can be saved in the data base, for a farther vote.

Syntax for alerting an attack:

CREATE VIEW View_name AS query;

Let's save our request as reported by report_vendor:

CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_com=product_com );

6. The axis can now be twisted by the sum function SUM():

SELECT SUM(summa) FROM report_vendor;

Axis mi and reached the result, however, for which we had a chance to win the contributions, the joint, the columns, which are counted, and the manifestations. So, sometimes for otrimannya the result should be thought without any other way. Then we bumped into two very important topics - counting the main points of that phenomenon. Let's talk about them in a report.

Count fields (stowpts)

SELECT stow_name_1, stow_name_2, stow_name_1*stow_name_2 AS enumerated_stow_name FROM table_name;

Another nuance is the key word AS, which was chosen for the task of the name of the column to be counted. In fact, for the help of which keyword, pseudonyms are given for whether they are stovptsіv. Is it necessary? For speedy reading of the code. For example, our manifestation might look like this:

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

Wait, what is so richly short and wiser.

Appearance

Ale sled memory, scho zavlennya - tse tables, so de stinks save data, it’s less likely to take them from other tables. First of all, if you change the data in the tables, the results of the presentation will also change. And in a different way, for a request before filing, there are requests for necessary data, so that the productivity of the DBMS decreases. Tozh zlovzhivat them not varto.

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. To know the number of current different models that are allowed by the A selection tool. 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 A selection tool.

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 the group), then the output data for the calculation, whether it be from the aggregate functions per day. And here the result of the count functions will be zero, and the result of 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.

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 the data base of this lesson on MS SQL Server, if the DBMS is not installed on your computer, then you can install it, crusty instructions .

We work with the data base of companies - Company1. Script for creating ts_єї database data, її table and burning table danimi - in file for cim send .

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 the same 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 determine the maximum salary of the spivrobitnikiv worker with the 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 commissions, so that you can 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 loaded 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 for creating tsієї databases, її tables and burning data tables - file for cim send .

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). Stovpets Units to collect data about the number of stuns, and Stovpets Money - about a penny sum, taken for filing a stun.

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