Data grouping¶

SELECT, in addition to retrieving specific column values, allows for aggregationof query results. This section will present different ways of grouping data.

Statistical functions¶

SQL provides a set of aggregate and statistical functions.

Counting the number of table rows¶

Counting the number of table records can be done by:

SELECT COUNT(*) FROM Product;

The * operator is an alias for all lines. The query will result in a table with a column named COUNT(*) and one record for the total number of parent table records.

In order to change the name of the result column, you can use the alias:

SELECT COUNT(*) AS 'Amount of products' FROM Product;

As part of the query, you can also specify a condition that should ultimately reduce the number of records searched:

SELECT COUNT(*) AS 'Amount of products'' FROM Product WHERE Name='Macbook';

Calculating the mean value¶

Calculation of the average value can be performed using the following query:

SELECT AVG(Price) AS 'Average product value' FROM Product;

Additionally, as standard, as for each SELECT query, we can enter theWHERE clause, which will return the result based on records that meet the condition given in the query:

SELECT AVG(Price) AS 'Average product value' FROM Product WHERE Price > 10;

Min and Max¶

Using queries:

SELECT MIN(Price) AS 'Minimum value product' FROM Product;

SELECT MAX(Price) AS 'Maximum value product' FROM Product;

Combining aggregate functions¶

It is possible to combine multiple aggregation functions within one query. Separate these functions with a comma, e.g.

SELECT AVG(Price) AS 'Average product value', COUNT(*) AS 'Amount of products' FROM Product;

Summing values¶

In order to sum the values from database tables, you can use the SUM function, which will add all values that meet the condition given in the query:

SELECT SUM(Price) AS 'The total value of Apple's products' FROM Product WHERE ProducerId <> 3;

Grouping query results¶

By default, the aggregation functions used return us single results within one criterion. As part of SQL, it is possible to group query results within a column or multiple columns:

SELECT column1, column2, ..., columnN FROM table1, table2, ..., tableN WHERE conditions GROUP BY column1, column2, ..., columnN;

Use of the GROUP BY clause¶

Thanks to this clause, it is possible to return, for example, the number of products for each manufacturer in the database:

SELECT COUNT(*) FROM Product GROUP BY ProducerId;

The result of the query will be a new table containing the number of records matching the number of manufacturers with correlated products in the database.

The next one represents the use of the GROUP BY clause with an additional condition:

SELECT MIN(Price), Max(Price) FROM Product WHERE Name='Keyboard' GROUP BY ProducerId;

The GROUP BY clause can additionally perform grouping over multiple columns:

SELECT MIN(Price), Max(Price) FROM Product WHERE Name='Keyboard' GROUP BY ProducerId, ClientId;

Grouping conditions¶

The grouping of query results can be extended with additional conditions, which are implemented using the HAVING clause. Such an inquiry may take the general form:

SELECT column1, column2, ..., columnN FROM table1, table2, ..., tableN WHERE where_conditions GROUP BY column1, column2, ..., columnN HAVING having_conditions;

Conditions of the having_conditions type allow you to limit the results of grouping queries, e.g. obtaining data on manufacturers who offer a number of products greater than 10:

SELECT COUNT(*) AS  'Sum_of_products' FROM Product GROUP BY ProducerId HAVING COUNT(*) > 10;

A more advanced example that returns products with the text Notebook in the name, grouped under manufacturers, where the manufacturer must have more than40 products:

SELECT SUM(Price) FROM Product WHERE Name LIKE 'Notebook %' GROUP BY ProducerId HAVING COUNT(*) > 40;

The HAVING clause can also be used together with column aliases, e.g.:

SELECT SUM(Price) as price_sum FROM Product GROUP BY ProducerId HAVING price_sum > 20;