Data grouping¶

In addition to get specific column values, SELECT also allows aggregation of query results. This section will introduce different ways to group data.

Statistical functions¶

SQL provides a set of aggregate and statistical functions.

Counting the number of records in the table¶

Counting the number of table records can be done using:

SELECT COUNT(*) FROM Product;

The operator * is an alias for all lines. The result of the query will be a table with a column called COUNT (*) and one record specifying the number of all table records.

To rename the result column, you can use an alias:

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

W ramach zapytania można określić też warunek, który docelowo powinien zmniejszyć liczbę przeszukiwanych rekordów:

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

Calculation of the average value¶

The calculation of the average value can be carried out using the following query:

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

In addition, as standard for each SELECT query, we can enter theWHERE clause, which will cause in the result being returned based on records meeting the condition specified in the query:

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

Min i Max¶

With queries:

SELECT MIN(Price) AS 'Product with minimum price' FROM Product;

SELECT MAX(Price) AS 'Product with maximum price' FROM Product;

Combining aggregate functions¶

Within one query it is possible to combine many aggregate functions. These functions are separated by a comma, e.g.

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

Adding values¶

In order to sum the values from the database tables, you can use the SUM function, which will add any values meeting the condition specified in the query:

SELECT SUM(Price) AS 'Sum of Apple' FROM Product WHERE ProducentId <> 3;

Grouping of query results¶

The aggregate functions used by default return single results within one criterion. As part of SQL, you can 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¶

Using this clause, it is possible to return, e.g. the number of Products for each of the producers in the database:

SELECT COUNT(*) FROM Product GROUP BY ProducentId;

The result of the query will be a new table containing the number of records coinciding with the number of producers, having correlated products in the database.

Another 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 ProducentId;

The GROUP BY clause can additionally group on multiple columns:

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

grouping conditions¶

Grouping of query results can be extended by additional conditions, which are implemented by the HAVING clause. This query may have the general form:

SELECT column1, column2, ..., columnN FROM table1, table2, ..., tableN WHERE conditions_where GROUP BY column1, column2, ..., columnN HAVING conditions_having;

conditions type conditions_having allow limiting the results of group queries, e.g. obtaining data on producers who offer more than 10 Products:

SELECT COUNT(*) AS  'Product sum' FROM Product GROUP BY ProducentId HAVING COUNT(*) > 10;

A more advanced example that returns Products with the text 'Notebook' in their name grouped within producers, where the producer must have more than '40' Products:

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

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

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