Calculations

Final functions

In SQL request expressions, it is often necessary to pre-process data. For this purpose, special functions and expressions are used.

Quite often, you need to know how many records correspond to a particular request,what is the sum of the values \u200b\u200bof some numerical column, its maximum, minimum and mean value. This serves the so-called final (statistical, aggregate) functions. The final functions processes the sets of records specified, for example, the expression WHERE. If they are included in the list of columns following the SELECT statement, the result table will contain not only the columns of the database table, but also the values \u200b\u200bcalculated using these functions. Read morelist of final functions.

• COUNT (Parameter ) - Returns the number of records specified in the parameter. If you want to get the number of all records, then you should specify the asterisk symbol (*). If you specify the name of the column as a parameter, the function will return the number of entries in which this column has values \u200b\u200bother than NULL. To find out how many different values \u200b\u200bcontains a column, you should specify the Distinct keyword before it. For example:

SELECT COUNT (*) FROM clients;

SELECT COUNT (SALE QUARD) FROM clients;

SELECT COUNT (Distinct sum_name) from customers;

Attempt to perform the following query will make an error message:

SELECT REGION, COUNT (*) FROM clients;

• SUM (Parameter ) - Returns the amount of the values \u200b\u200bof the column specified in the parameter. The parameter may be both an expression containing the name of the column. For example:

SELECT SUM. (Value_name) from customers;

This SQL expression returns a table consisting of a single column and one record and containing the sum of all certain values \u200b\u200bof the column amount from the client's table.

Suppose that in the source table, the value of the column amount is expressed in rubles, and we need to calculate the total amount in dollars. If the current exchange rate is, for example, 27.8, then it is possible to obtain the desired result using an expression:

SELECT SUM. (Sum_name * 27.8) from customers;

• AVG (Parameter ) - Returns the arithmetic average of all values \u200b\u200bspecified in the column parameter. The parameter may be an expression containing the name of the column. For example:

SELECT AVG (sum_name) from customers;

SELECT AVG (sum_name * 27.8) from customers

Where region<> "North_3Apad";

• Mach (parameter ) - Returns the maximum value in the column specified in the parameter. The parameter may also be an expression containing the name of the column. For example:

SELECT MAX (sum__nce) from customers;

Select Mach (sum_name * 27.8) from customers

W Here. Region<> "North_3Apad";

• MIN (Parameter ) - Returns the minimum value in the column specified in the parameter. The parameter may be an expression containing the name of the column. For example:

SELECT MIN (SALE ORDER) from customers;

SELECT MIN (sum__nce * 27. 8) from customers

W Here. Region<> "North_3Apad";

In practice, it is often necessary to obtain a final table containing the total, averaged, maximum and minimum values \u200b\u200bof numerical columns. To do this, use group by group (Group by) and final functions.

SELECT REGION, SUM (Sumader) from customers

Group by region;

The result table for this request contains the names of the regions and the total (general) amount of orders of all customers from the respective regions (Fig. 5).

Now consider a request to receive all the final data by region:

SELECT REGION, SUM (SUP OPTION), AVG (Sum_name), max (sum_name),MIN (sum_name)

From clients

Group by region;

The source and result tables are shown in Fig. 8. In the example, only the North-West region is presented in the source table with more than one record. Therefore, in the result table for it, various outcomes are given different values.

Fig. 8. Final table amount of orders by region

When using the final functions in the list of columns in the SELECT statement, the headers corresponding to them in the result table have the appearance of ExPR1001, ExPR1002, etc. (or something similar, depending on the implementation of SQL). However, headlines for the values \u200b\u200bof the final functions and other columns you can set at your discretion. To do this, sufficiently after the column in the SELECT statement specify the expression of the type:

AS Title_stolbets

The AS keyword (as) means that in the result table, the corresponding column must have a title specified after AS. The appointed header is also called a pseudonym. In the following example (Fig. 9), pseudonyms are set for all calculated columns:

SELECT REGION,

SUM. (Sum_name) AS [Total order amount],

AVG. (The sum of the order) AS [average order amount],

Max (sum_name) as maximum

MIN. (Sum_name) AS minimum,

From clients

Group by region;

Fig. 9. The final table of orders by regions using column alias

Pseudonyms, consisting of several words separated by spaces, are square brackets.

The final functions can be used in SELECT and HAVING expressions, but they cannot be used in the WHERE expression. OneRATER HAVING is similar to the WHERE operator, but in contrast to where it selects records in groups.

Suppose it is required to determine in which regions more than one client. To this end, you can use this request:

SELECT REGION, COUNT (*)

From clients

Group by region Having Count (*)\u003e 1;

Functions for processing values

When working with data, they often have to process them (convert to the desired form): Select some substring in the line, remove the leading and final gaps, roundate the number, calculate the square root, determine the current time, etc. The following three types of functions are available in SQL:

• string functions;
• numeric functions;
• date-time functions.

String functions

String functions are taken as the string parameter and return after it processing the string or NULL.

• Substring (String from Start) - Returns a substring obtained from the string that is specified as a parameterline . Substring It starts with the character, the sequence number of which is specified in the start parameter, and has the length specified in the length parameter. The numbering of the string symbols is conducted from left to right, starting with 1. Square brackets here indicate only the fact that the expression concluded in them is not mandatory. If expressionFor Length not used, then the substring is returned fromstart and until the end of the source line. Parameter valueshome and Length Must be selected so that the desired substring is really inside the source line. Otherwise, the SUBSTRING function will return NULL.

For example:

Substring ("Dear Masha!" FROM 9 FOR 4) - Returns "Masha";

SUBSTRING ("Dear Masha!" From 9) -Ards "Masha!";

SUBSTRING ("Dear Masha!" From 15) is NULL.

You can use this feature in SQL expression, for example, as follows:

SELECT * FROM customers

Where substring (region from 1 for 5) \u003d "North";

• Upper (string ) - transfers all the characters specified in the string parameter to the upper case.
• Lower ) - transfers all the characters specified in the string parameter to the lower register.
• Trim (Leading | Trailing | Both ["Symbol"] FROM Row ) - Deletes the leading (LEADING), final (Trailing) or those and others (Both) characters from a string. By default, a remote symbol is a space (""), so it can not be indicated. Most often, this feature is used precisely to remove spaces.

For example:

Trim (LEADING "" from "City of St. Petersburg") rotates "City of St. Petersburg";

Trim (TRALING "" From "City of St. Petersburg") Returns the city of St. Petersburg;

Trim (Both "" from "City of St. Petersburg") - Returns the city of St. Petersburg;

Trim (Both from "City of St. Petersburg") - Returns the city of St. Petersburg;

Trim (Both "Mr." From "City of St. Petersburg") - Returns the "St. Petersburg Ood".

Among these functions are the most commonly used - substring () and trim ().

Numeric functions

Numeric functions as a parameter can receive data not only numerical type, but always return the number or null (indefinite value).

• POSITION ( targetstrock in String) - looks for the entry of the target line to the specified string. In the case of a successful search, returns the position number of its first character, otherwise - 0. If the target line has zero length (for example, a string "), then the function returns 1. If at least one of the parameters is NULL, NULL is returned. The numbering of the string symbols is carried out from left to right, starting from 1.

For example:

Position ("E" in "Hello everyone") - Returns 5;

Position ("All" in "Hello everyone") - Returns 8;

POSITION ("Hello everyone") - Returns 1;

Position ("Hi!" In "Hello everyone") - Returns 0.

In the table clients (see Fig. 1) The address column contains, except the city name, the zip code, the name of the street and other data. You may need to choose client records living in a specific city. So, if you need to choose records related to customers living in St. Petersburg, you can use the following SQL request expression:

SELECT * FROM customers

Where Position ("St. Petersburg" in address)\u003e 0;

Note that this simple request to select data can be formulated otherwise:

SELECT * FROM customers

Where the address Like "% Petersburg%";

• Extract (Parameter ) - extracts an element from the type of date-time or from the interval. For example:

Extract (Month From Date "2005-10-25") -returns 10.

• Character_Length (string ) - Returns the number of characters in the string.

For example:

Character_Length ("Hello everyone") - Returns 11.

• OCTET_LENGTH (string ) - Returns the number of octets (bytes) in the string. Each symbol of Latin or Cyrillic is one byte, and the symbol of the Chinese alphabet is two bytes.
• CARDINALITY (Parameter ) - takes a collection of elements as a parameter and returns the number of elements in the collection (cardinal number). The collection can be, for example, with an array or multiset containing elements of various types.
• ABS (Number ) - Returns the absolute value of the number. For example:

ABS (-123) is 123;

ABS (2 - 5) - Returns 3.

• MO D (number1, number2 ) - Returns the balance of integer division of the first number to the second. For example:

Mod (5, s) - Returns 2;

Mod (2, s) - Returns 0.

• LN (Number ) - Returns the natural logarithm of the number.
• EXR (number) - returns (The basis of the natural logarithm to the extent number).
• Power (number1, number2 ) - Returns the number1number2 (number1 to the degree number2).
• SQRT (Number ) - Returns a square root from among.
• Floor (Number ) - Returns the largest integer that does not exceed the specified parameter (rounding to a smaller direction). For example:

Floor (5.123) - Returns 5.0.

• CEIL (Number) or Ceiling (number ) - Returns the smallest integer that is not less than the rounding parameter in a large direction). For example:

Ceil (5.123) - Returns 6. 0.

• Width_bucket. (number1, number2, numerose, number4) Returns an integer in the range between 0 and number4 + 1. Parameters number2 and numerose specify a numerical segment, divided into equilibrium intervals, the number of which is set by the number 4. The function determines the interval number in which the value number1. If the number1 is outside the specified range, then the function returns 0 or number 4 + 1. For example:

Width_bucket (3.14, 0, 9, 5) - Returns 2.

Date-time functions

The SQL language has three functions that return the current date and time.

• Current_date. - Returns the current date (type DATE).

For example: 2005-06-18.

• CURRENT_TIME (Number ) - Returns the current time (Time Type). An integer parameter indicates the accuracy of the submission of seconds. For example, with a value of 2 seconds will be presented with an accuracy of hundredths (two digits in the fractional part):

12:39:45.27.

• Current_TimeStamp (Number ) - Returns the date and time (Type TimeStamp). For example, 2005-06-18 12: 39: 45.27. An integer parameter indicates the accuracy of the submission of seconds.

Note that the date and time returned by these functions have no symbol type. If you want to submit them in the form of character strings, then for this you should use the Cast () conversion function.

Date-time functions are usually applied in inserting requests, update and delete data. For example, when writing a sales information in a specially provided for this, the column is made by the current date and time. After summing up for the month or quarter, sales data for the reporting period can be deleted.

Calculated expressions

The calculated expressions are constructed from constants (numeric, string, logical), functions, field names and data of other types by connecting their arithmetic, string, logical and other operators. In turn, expressions can be combined through operators in more complex (composite) expressions. Round brackets are used to manage the procedure for calculating expressions.

Logic operators And, or and not and functions Were reviewed earlier.

Arithmetic operators:

• + - addition;
• - - subtraction;
• * - multiplication;
• / - division.

String operator Only one is a concatenation operator or string gluing (| |). In some SQL implementations (for example, Microsoft Access), instead of (| |), a symbol (+) is used. Concatentation operator attributes a second string to the end of the first example, expression:

"Sasha" | | "Loves" | | "Masha"

will return as a result of the string "Sashalubit Masha".

When making expressions, it is necessary to ensure that operands operators have permissible types. For example, the expression: 123 + "Sasha" is unacceptable because the arithmetic addition operator is applied to the string operand.

Calculated expressions may be after the SELECT statement, as well as in the terms of the conditions of Where and Havi operatorsNG.

Consider several examples.

Let the Sales Table contain the Type type columns, the quantity and price, and we need to know the revenue for each type of product. For this, it is enough in the list of columns after the SELECT operator turn on the expression quantity * Price:

SELECT type_dashovar, quantity, price, quantity * PriceAs

TOTAL FROM SALE;

It uses the AS keyword (as) to set a column pseudonym with calculated data.

In fig. 10 shows the source table of sale and the result table of the query.

Fig. 10. Request result with revenue calculation for each type of goods

If you want to know the overall revenue from the sale of all goods, it is enough to apply the following query:

SELECT SUM. (Quantity * price) from sale;

The following query contains calculated expressions and in the list of columns, and in the WHERE operator condition. It chooses from the table of sale those goods, revenue from the sale of which more than 1000:

SELECT type_tovar, quantity * price AS Total

From sale

WHERE Number * Price\u003e 1000;

Suppose you need to get a table in which two columns:

Goods containing the type of goods and the price;

Total containing revenue.

Because it is assumed that in the source table of the sale column type_lovar is symbolic (char), and the price column is a numeric, then when combining the (gluing) of data from these columns, it is necessary to bring a numerical type to the symbol using the CAST () function. The request that performs this task looks like this (Fig. 11):

SELECT type_stovar | | "(Price:" | | Cast (price as char (5)) | | ")" AS Product, Quantity * Price AS Total

From sale;

Fig. 11. Request result with a union of database data in one column

Note. In Microsoft Access, a similar request will have the following form:

SELECT type_stovar + "(Price:" + CStr. (Price) + ")" AS product,

Quantity * Price AS Total

From sale;

Conventional expressions with CASE operator

In the usual programming languages \u200b\u200bthere are conditional transition operators that allow you to control the computing process depending on whether or not has a certain condition. In SQL language, such an operator is CASE (case, circumstance, copy). In SQL: 2003, this operator returns a value and, therefore, can be used in expressions. It has two main forms that we will consider in this section.

Case operator with values

Case operator with values \u200b\u200bhas the following syntax:

Case Checked_New

WHEN VALUE1 THEN RESULT1

When the value is 0 THEN resuletat2

. . .

WHEN MENUE N THEN RESULT N

ELSE Results

In the case when checked Equally value1 , Case operator returns valueresult1 , Specified after the key word then. Otherwise the verifiable_dation is compared withmeaning2 , and if they are equal, then the value of the result2 is returned. Otherwise, the verified value is compared with the following value indicated after the key word (when), etc. If the verifiable_ value is not equal to one of these values, then the value is returnedresult X. indicated after the keyword ELSE (otherwise).

ELSE keyword is not mandatory. If it is missing and none of the values \u200b\u200bthat are subject to comparison, not equal to the valued value, the Case operator returns NULL.

Suppose, based on the client table (see Fig. 1), it is required to get a table in which the names of the regions are replaced by their code numbers. If there are not too many different regions in the source table, then it is convenient to use the request with the CASE operator to solve this task:

Select name, address,

Case Region

WHEN "MOSCOW" THEN "77"

WHEN "Tver Region" Then "69"

. . .

ELSE Region

AS Region code

From customers;

Case operator with search terms

The second form of the CASE operator assumes its use when searching in the table of those records that satisfy a certain condition:

Case.

WHEN CONDITION 1 THEN RESULT1

WHEN WAI2 THEN RESULT2

. . .

WHEN CONDITION N THEN Result n

ELSE Results

Case operator checks whether condition1 is true for the first entry in the set defined by the WHERE operator, or in the entire table, if there is no WHERE. If so, Case returns the value of the result1. Otherwise, a condition2 is checked for this record. If it is true, the value of the result2 is returned, etc. If none of the conditions are executed, then the value is returned.X. shown after the key of the word ELSE.

ELSE keyword is not mandatory. If it is missing and none of the conditions is performed, the Case operator rotates NULL. After the operator containing Case is executed for the first record, goes to the next record. So continues until the entire set of records is processed.

Suppose, in the book table (name, price), the column is NULL if the corresponding book is not available. The following request returns a table in which the text "Not available" appears instead of NULL:

SELECT name

Case.

When the price is NULL THEN "Out of stock"

ELSE CAST (price as char (8))

AS price

From books;

All values \u200b\u200bof the same column must have the same types. Therefore, this query uses the CAST type conversion function to bring the number of column values. Price to symbolic type.

Please note that instead of the first form, the CASE operator can always be used second:

Case.

WHEN VERSEMENT_VENUE \u003d Value1 THEN result1

WHEN VERSEMENT_VENSE \u003d Value2 THEN result2

. . .

WHEN VERSEMENT_VENSE \u003d ValueN then pseltytn.

Else Resoletath

NULLIF and COALESCE functions

In some cases, especially in the data update requests (UPDATE operator), it is convenient to use more compact functions nullif () (NULL, if) and coalesce () (, if) and coalesce ().

NULLIF function ( meaning1, value2) Returns NULL if the value of the first parameter corresponds to the value of the second parameter, in case of inconsistencies, the value of the first parameter is returned unchanged. That is, if the equality value1 \u003d value2 is performed, the function returns NULL, otherwise the value value1.

This feature is equivalent to the CASE operator in the following two forms:

• Case value1

WHEN RECOMMENDS2 THEN NULL

ELSE value1

• Case.

When value1 \u003d value2 THEN NULL

ELSE value1

Coalesce feature ( value1, value2, ...,n value n) accepts a list of values \u200b\u200bthat can be both specific and uncertain (NULL). The function returns a specific value from the list or NULL if all values \u200b\u200bare not defined.

This feature is equivalent to the following CASE operator:

Case.

WHEN VALUE 1 IS NOT NULL THEN value 1

When Value 2 IS NOT NULL THEN Value 2

. . .

When N IS NOT NULL THEN value N

Else Null.

Suppose that in the book table (name, price), the price column is NULL, if the corresponding book is not available. The following request returns a table in which insteadNULL The text "Not In Stock" is displayed:

SELECT Name, Coalesce (Cast (price as char (8)),

"Not available") AS Price

From books;

SQL - lesson 11. Total functions, calculated columns and views

The final functions are also called statistical, aggregate or summing. These functions handle a set of rows for counting and returning one value. There are only five such functions:

• AVG () The function returns the average column value.


• COUNT () The function returns the number of rows in the column.


• Max () The function returns the largest value in the column.


• MIN () The function returns the smallest value in the column.


• SUM () The function returns the amount of column values.

With one of them - Count () - We already met in lesson 8. . Now let's get acquainted with the rest. Suppose we wanted to learn the minimum, maximum and average price of books in our store. Then from the price table (PRICES) need to take the minimum, maximum and mean value of the column. Request simple:

Select min (Price), Max (Price), AVG (Price) from Prices;

Now, we want to know how much it brought to us the provider of the "Print House" (id \u003d 2). Make such a request is not so simple. Let's think about how to make it:

1. First, you need to select the identifiers (id_incoming) of those supplies from the delivery table (id_incoming) (id \u003d 2) from the Supplies (id \u003d 2):

2. Now from the table of supplies log (Magazine_incoming), you need to choose goods (ID_PRODUCT) and quantity (quantity), which were found in the supply points in paragraph 1. That is, the request from paragraph 1 becomes embedded:

3. Now we need to add prices to the resulting table for found products that are stored in the price table (PRICES). That is, we will need to combine the tables of supplies (Magazine_incoming) and prices (PRICES) on the ID_Product column:

4. The resulting table clearly lacks the column amount, that is calculated column. The ability to create such columns is provided in MySQL. To do this, you just need to specify the name of the calculated column in the query and what it must calculate. In our example, this column will be called Summa, and it will calculate it will be a product of Quantity columns and Price. The name of the new column is separated 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 \u003d prices.id_product AND id_incoming \u003d (SELECT id_incoming FROM incoming WHERE id_vendor \u003d 2);

5. Excellent, we have only to sum up the Summa column and finally find out what amount we brought the goods supplier "House of Printing". Syntax to use the SUM () function:

SELECT SUM (SHUMBACE NAME) FROM Name_Table;

We know the name of the column - Summa, but we do not have the table name, as it is the result of the query. What to do? For such cases in MySQL there are views. The view is a selection request that is assigned a unique name and which can be saved in the database for later use.

Representation creation syntax is as follows:

CREATE VIEW Representative name AS query;

Let's save our request as the view named 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_product \u003d prices.id_product AND id_incoming \u003d (SELECT id_incoming FROM incoming WHERE id_vendor \u003d 2 );

6. Now you can use the total SUM () function:

SELECT SUM (Summa) from Report_vendor;

So we achieved the result, the truth for this we had to use invested requests, associations, calculated columns and presentations. Yes, sometimes to obtain the result, it is necessary to think without it anywhere. But we touched two very important topics - calculated columns and presentations. Let's talk about them in more detail.

Calculated fields (columns)

On the example, we looked at the mathematical calculated field today. Here I would like to add that it is possible to use not only the operation of multiplication (*), but also subtraction (-), and addition (+), and division (/). The syntax is as follows:

SELECT_name_stolbet_1 ,_stolbet_name_2 ,_stolbet_name_1 *_name_22 AS_name_stolbet_stolbet_name_Table;

The second nuance is the AS keyword, we used it to set the name of the calculated column. In fact, with the help of this keyword, pseudonyms are defined for any columns. Why do you need it? To reduce and read the code. For example, our representation could 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 \u003d B.ID_PRODUCT AND ID_INCOMING \u003d (SELECT ID_INCOMING FROM INCOMING WHERE ID_VENDOR \u003d 2);

Agree that it is so much shorter and clearer.

Representation

The syntax of the creation of representations was already considered. After creating views, they can be used in the same way as tables. That is, perform requests to them, filter and sort the data, combine some views with others. On the one hand, this is a very convenient way to store component complex queries (as in our example).

But it should be remembered that the views are not a table, that is, they do not store data, but only extract them from other tables. From here, firstly, when changing data in the tables, the results of the presentation will also change. And secondly, when requesting a view, the necessary data is found, that is, the performance of the DBMS decreases. Therefore, it is not necessary to abuse them.

The SUM function in the SQL language, despite its simplicity, is used quite often when working with the database. With it, it is convenient to obtain some intermediate or final results, without resorting to the help of subsidiary tools of DBMS.

Syntax function

In most SQL languages, SUM syntax is the same - only the field name or some arithmetic effect of several of them are used as an argument, which requires summation.

In exceptional cases, it is possible to transmit a specific value in the form of a number or variable, however, such "schemes" are practically not used, since they do not carry great value. Below is the syntax of the SQL function:

sUM (A) - here as a parameter A used some numeric value or expression

It is worth noting that in front of the parameter you can set keywords, for example, Distinct or All, which will only take unique or all values, respectively.

Example of using SUM in SQL

For the final understanding of the principle of operation of the function, it is worth considering several examples. SQL SUM can be used both as a returned result and the role of an intermediate value, for example, to verify the condition.

For the first case, consider the option when you want to return the sales amount for each product, given that the number of purchases made can be in a plural. To obtain the result, it will be enough to perform the following query:

Select product, SUM (total) from sale Groupby goods;

The answer to this command will be a unique list of products with the final purchase amount for each of them.

For the second example, it is necessary to obtain a list of products, the amount of sales by which exceeded a certain value, for example, 100. It is possible to obtain the result of this task in several ways, the most optimal of which is the execution of one request:

Select Goods from (Select Product, SUM (total) AS Amount FROM SALE) WHERE Amount\u003e 100.

In this tutorial you will learn how to use MySQL sUM function with syntax and examples.

Description

MySQL feature SUM. Returns the total value of the expression.

Syntax

MYSQL syntax SUM functions:

Or MySQL syntax SUM functions when grouping results for one or more columns:

Parameters or arguments

expression1, Expression2, ... expression_n - expressions that are not encapsulated in the SUM function and must be included in the GROUP BY offer at the end of the SQL operator.

aggregate_Expression is a column or expression that will be summed up.

tables - Tables from which you want to receive records. There must be at least one table specified in the FROM clause.

Where Conditions - optional. These are the conditions that must be completed for the selected entries.

Application

The SUM function can be used in the following versions of MySQL:

• Mysql 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MYSQL 3.23

An example with one expression

Consider the examples of MySQL functions of the SUM to understand how to use the SUM function in MySQL.

For example, you need to find out what cumulative overall salary of all employees whose salary exceeds $ 20,000 per year.

In this example, the SUM function, the expression of SUM (SALARY) was assigned the pseudonym "Total Salary". As a result, when returning a set of results, Total Salary will be displayed as the field name.

Example with the use of Distinct

You can use the DistinCT offer in the SUM function. For example, the SQL statement below returns the combined total salary of the unique wages, where the salary is higher than $ 20,000 per year.

If two salary were 10,000 dollars a year, only one of these values \u200b\u200bwould be used in the SUM function.

Example using formula

The expression contained in the SUM function does not have to be one field. You can also use the formula. For example, you can calculate the General Commission.

Mysql

SELECT SUM (Sales * 0.05) AS "Total Commission" from Orders;

SELECT SUM (Sales * 0.05) AS "TOTAL COMMISSION" From orders;

Example using GROUP by

In some cases, you will need to use the GROUP BY supply with SUM function.

For example, you can use the SUM function to return the department and the total amount of Sales (sales in the appropriate department).