- SQL Select
- SQL Select Distinct
- SQL Where
- SQL And, Or, Not
- SQL Order By
- SQL Insert Into
- SQL Null Values
- SQL Update
- SQL Delete
- SQL Select Top
- SQL Min and Max
- SQL Count, Avg, Sum
- SQL Like
- SQL Wildcards
- SQL In
- SQL Between
- SQL Aliases
- SQL Joins
- SQL Inner Join
- SQL Left Join
- SQL Right Join
- SQL Full Join
- SQL Self Join
- SQL Union
- SQL Group By
- SQL Having
- SQL Exists
- SQL Any, All
- SQL Select Into
- SQL Insert Into Select
- SQL Case
- SQL Null Functions
- SQL Stored Procedures
- SQL Comments
- SQL Operators

TOP
# SQL-Lesson 8. Data grouping (GROUP BY)

## 1. Creating groups (GROUP BY)

## 2. Filtering groups (HAVING)

## 3. Grouping and sorting

Data grouping allows you to divide all data into logical sets, which makes it possible to perform statistical calculations separately in each group.

Groups are created using the GROUP BY statement of the SELECT operator. Let's consider an example.

Run SQL`SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product`

With this request, we extracted information on the number of products sold in each month. The operator SELECT orders to output two columns Product - the name of the product and Product_num - the calculated field that we created to display the quantity of sold products (field formula SUM(Quantity)). The GROUP BY clause tells the DBMS to group data by the Product column.

It is also worth noting that GROUP BY must come after the WHERE clause and before ORDER BY.

Just as we filtered rows in a table, we can filter on grouped data. For this, there is the HAVING operator in SQL. Let's take the previous example and add group filtering.

Run SQL`SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity) > 4000`

We can see that after the number of sold goods was calculated for each product, the DBMS "cut off" those products that were sold less than 4,000 units.

As you can see, the HAVING operator is very similar to the WHERE operator, but there is a significant difference between them: WHERE filters data before it is grouped, and HAVING filters after grouping. Thus, the rows that were removed by the WHERE clause will not be included in the group. So, WHERE and HAVING operators can be used in the same sentence. Consider an example:

Run SQL`SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct WHERE Product <> 'Skis Long' GROUP BY Product HAVING SUM(Quantity) > 4000`

We added the WHERE operator to the previous example, where we specified the product "Skis Long", which in turn affected the grouping by the HAVING operator. As a result, we can see that the product "Skis Long" did not make it into the list of groups with more than 4,000 sold products.

As with normal data sampling, we can sort the groups after grouping with the HAVING operator. For this, we can use the already familiar operator ORDER BY. In this situation, its application is similar to the previous examples. Example:

Run SQL`SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity) > 3000 ORDER BY SUM(Quantity)`

or simply specify the field number in the order in which we want to sort:

Run SQL`SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity) > 3000 ORDER BY 2`

We see that in order to sort the summary results, we just need to write the clause with ORDER BY after the operator HAVING.

MS Access does not support sorting groups by column aliases, that is, in our example, to sort the values, we will not be able to write ORDER BY Product_num at the end of the query.

- 1. What is VBA, basic concepts.
- 2. Debugger (Debugger)
- 3. Work with sheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties (Properties)
- 6.1. Data types (Variables)
- 6.2. Data Types (Continued)
- 7.1. Conditions (Conditions)
- 7.2. Terms (Continued)
- 8.1. Loops (Loops)
- 8.2. Cycles (Continued)
- 9. Procedures and functions
- 10. Dialog windows
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls (Controls)
- 12.3. Control elements (Continued)
- 12.4. Control elements (Exercises)
- 13.1. Arrays (Arrays)
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. SQL language, basic concepts.
- 2. Data selection (SELECT)
- 3. Data sorting (ORDER BY)
- 4. Data filtering (WHERE)
- 5. Use of metacharacters (LIKE)
- 6. Calculation (calculation) fields
- 7. Data processing functions
- 8. Data grouping (GROUP BY)
- 9. Sub-requests
- 10. Combination of tables (INNER JOIN)
- 11. OUTER JOIN
- 12. Combined requests (UNION)
- 13. Adding data (INSERT INTO)
- 14. Creating tables (CREATE TABLE)

- Import of currency rates from the NBU website
- Sum written in English
- Unpivottables (Unpivot)
- Function Google Translate
- Camel Case Function
- Snake Case Function
- Kebab Case Function
- Break Case Function
- Sentence Case Function
- Title Case Function
- Fuzzy Lookup Function
- Function GETSUBSTR
- YouLibreCalc extension for LO Calc