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. Two applications must be distinguished 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. 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).
If you want to run database queries from this tutorial in MS SQL Server, but this DBMS is not installed on your computer, then you can install it using the instructions at this link .
First we will work with the company database - Company1. The script for creating this database, its tables and filling the tables with data is in the file at this link .
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, we use the following query (on MS SQL Server - with the preceding construction USE company1;):
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 query (on MS SQL Server - with the prefix USE company1;):
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.
We need to find out the maximum salary of employees in department number 42. To do this, write the following query (on MS SQL Server - with the prefix USE company1;):
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.
Let’s say you want to find out the average length of service of employees in department number 42. To do this, write the following query (on MS SQL Server - with the preceding construction USE company1;):
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 (on MS SQL Server - with the prefix USE company1;):
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 (on MS SQL Server - with the preceding construction USE company1;):
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 .
We will work with the "Ads Portal 1" database. The script for creating this database, its table and filling the data table is in the file at this link .
Example 12. So, 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 query (on MS SQL Server - with the preceding construction USE adportal1;):
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 in which part of each category the submission was made greatest number advertisements We write the following query (on MS SQL Server - with the preceding construction USE adportal1;):
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
How can I find out the number of PC models produced by a particular supplier? How to determine the average price of computers that have the same specifications? 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 quantity various 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, should 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.
SQL - Lesson 11. Total functions, calculated columns and views
Total functions are also called statistical, aggregate, or sum functions. These functions process a set of strings to count and return a single value. There are only five such functions:- AVG() Function returns the average value of a column.
- COUNT() Function returns the number of rows in a column.
- MAX() Function returns the largest value in a column.
- MIN() Function returns the most small value in the column.
- SUM() The function returns the sum of the column values.
SELECT MIN(price), MAX(price), AVG(price) FROM prices;
Now, we want to find out how much the goods were brought to us by the supplier "House of Printing" (id=2). Making such a request is not so easy. Let's think about how to compose it:
1. First, from the Supplies (incoming) table, select the identifiers (id_incoming) of those deliveries that were carried out by the supplier "Print House" (id=2):
2. Now from the Supply Journal table (magazine_incoming) you need to select the goods (id_product) and their quantities (quantity), which were carried out in the deliveries found in point 1. That is, the query from point 1 becomes nested:
3. Now we need to add to the resulting table the prices for the found products, which are stored in the Prices table. That is, we will need to join the Supply Magazine (magazine_incoming) and Prices tables using the id_product column:
4. The resulting table clearly lacks the Amount column, that is calculated column. The ability to create such columns is provided in MySQL. To do this, you just need to specify in the query the name of the calculated column and what it should calculate. In our example, such a column will be called summa, and it will calculate the product of the quantity and price columns. 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= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
5. Great, all we have to do is add up the summa column and finally find out how much the supplier “House of Printing” brought us the goods for. The syntax for using the SUM() function is as follows:
SELECT SUM(column_name) FROM table_name;
We know the name of the column - summa, but we do not have the name of the table, since it is the result of a query. What to do? For such cases, MySQL has Views. A view is a selection query that is given a unique name and can be stored in a database for later use.
The syntax for creating a view is as follows:
CREATE VIEW view_name AS request;
Let's save our request as a 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= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2 );
6. Now you can use the final function SUM():
SELECT SUM(summa) FROM report_vendor;
So we achieved the result, although for this we had to use nested queries, joins, calculated columns and views. Yes, sometimes you have to think to get a result, without this you can’t get anywhere. But we touched on two very important topics - calculated columns and views. Let's talk about them in more detail.
Calculated fields (columns)
Using an example, we looked at a mathematical calculated field today. Here I would like to add that you can use not only the multiplication operation (*), but also subtraction (-), addition (+), and division (/). The syntax is as follows:SELECT column_name 1, column_name 2, column_name 1 * column_name 2 AS calculated_column_name FROM table_name;
The second nuance is the AS keyword, we used it to set the name of the calculated column. In fact, this keyword is used to set aliases for any columns. Why is this necessary? For code reduction and readability. For example, our view 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= B.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
Agree that this is much shorter and clearer.
Representation
We have already looked at the syntax for creating views. Once views are created, they can be used in the same way as tables. That is, run queries against them, filter and sort data, and combine some views with others. On the one hand, this is a very convenient way to store frequently used complex queries (as in our example).But remember that views are not tables, that is, they do not store data, but only retrieve it from other tables. Hence, firstly, when the data in the tables changes, the presentation results will also change. And secondly, when a request is made to a view, the required data is searched, that is, the performance of the DBMS is reduced. Therefore, you should not abuse them.
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 case for creating calculated (derived) field the SELECT list must contain some SQL expression. These expressions use arithmetic operations addition, subtraction, multiplication and division, as well as built-in functions of the SQL language. 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. For this purpose in SELECT statement The GROUP BY clause is used. 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 following types 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 additional opportunity"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.
The SUM function in the SQL language, despite its simplicity, is used quite often when working with a database. With its help, it is convenient to obtain some intermediate or final results without resorting to the help of auxiliary DBMS tools.
Function syntax
In most languages SQL syntax sum is the same - only the name of the field or some arithmetic operation of several of them is used as an argument, over which the sum is required.
In exceptional cases, it is possible to transmit a specific value as a number or variable, but such “schemes” are practically not used, since they do not carry much value. Below is the function syntax in SQL:
sum(a) - here some numeric value or expression is used as parameter a
It is worth noting that before the parameter you can set keywords, for example DISTINCT or ALL, which will take only unique or all values, respectively.
Example of using SUM in SQL
To fully understand how the function works, it is worth considering several examples. In SQL, SUM can be used both as a return result and as an intermediate value, for example, to test a condition.
For the first case, consider the option when you need to return the amount of sales for each product, taking into account that the number of purchases made can be in the plural. To get the result, it will be enough to run the following query:
SELECT Product, sum(PurchaseAmount) FROM Sales GroupBy Product;
In response to this command there will be a unique list of products with the total purchase amount for each of them.
For the second example, you need to get a list of products whose sales amount exceeded a certain value, for example, 100. You can get the result for this task in several ways, the most optimal of which is to execute one request:
SELECT Product FROM (SELECT Product, sum(Purchase Amount) as Amount FROM Sales) WHERE Sum > 100.