SQL GROUP BY 

SQL GROUP BY 

The GROUP BY command is used in SQL to aggregate multiple results and use a total function on a result group. On a table that contains all the sales of a store, it is standard conceivable example of listing grouping identical standard sales customers and getting the all out cost of purchases for each customer.

The GROUP BY provision allows you to organize the lines of a group query. Groups are standard determined columns that you specify in the GROUP BY provision. 

L'instruction GROUP BY dans SQL est utilisée pour coordinator des données identiques en groupes à l'aide de certaines fonctions. Si une colonne particulière a les mêmes valeurs dans différentes lignes, elle organisera ces lignes dans un groupe. 

Important focuses:The GROUP BY statement is used with the SELECT statement. 
In the application,  The GROUP BY provision is placed after the WHERE proviso. 
In the application, The GROUP BY statement is placed before the ORDER BY condition if used.

The following illustrates the syntax of the GROUP BY provision:

GROUP BY Syntax:

SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
ORDER BY column1, column2;

GROUP BY Example:

Ex 1:

SELECT COUNT(Client_ID), Country
FROM Client
GROUP BY Country;

Ex 2:

SELECT COUNT(Client_ID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(Client_ID) DESC;

Utilizing other measurable capacities :

There are a few capacities that can be utilized to control different records, these are measurable conglomeration works, the fundamental ones are: 

AVG() :  to ascertain the normal of a worth set. Tells you the cost of the normal container for every client 

Check() :  to tally the quantity of lines included. Tells you the number of buys every client made 

MAX()  : to recuperate the most elevated worth. Functional to know the most costly buy 

MIN() to recuperate the littlest worth. Helpful for instance to know the date of a client's first buy 

SUM() :  to ascertain the amount of a few lines. For instance, you can see the all out of each of the a client's buys 

Sql Worker Gathering BY, provision and aggregate functions

In practice, the Gathering BY provision is often used with aggregated functions to generate summary compatibilities.

An aggregated function performs a calculation on a group and returns a remarkable standard group value. Standard example, Tally() returns the number of lines in each group. Other commonly used aggregate functions are Entirety(), AVG() (average), MIN() (least), MAX() (most extreme).

The Gathering BY provision coordinates the group lines and an aggregated function returns the list of references (account, min, max, average, sum, then forth) for each group.

Standard example, the following query returns the number of orders placed standard the standard customer year:

Select
customer_id, customer_id,
YEAR (order_date) order_year,
ORDER_PLACED Tally (order_id)
Where
customer_id IN (1, 2)
Standard GROUP BY
customer_id, customer_id,
ORDER BY YEAR 
Authority Standard
customer_id;

Provision SQL Worker GROUP BY - example of expression

If you want to refer to a column or joint that is not listed in the Gathering BY provision, you should use that column as an input for an aggregate function. Otherwise, you will get a vehicle error there is no guarantee that the column or expression returns a single standard group value. For example, the following query fails:

SELECT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) ORDER BY customer_id;

Moremore examples of Provision GROUP BY

Let's take other examples to understand how the Gathering BY provision works.

Use the GROUP BY provision with the Check() feature example

The following query returns the number of customers in each city:

SELECT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id;

SQL Worker GROUP  BY - EXEMPLE DE COMPTE

In this example 

In this example, the GROUP BY provision includes standard city customers and the Tally() function returns the number of customers in each city.

Similarly, the following query returns the number of standard state and city standard customers.

SELECT DISTINCT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) ORDER BY customer_id;

Provision SQL Worker Gathering BY - sample products columns

Use of Gathering BY statement with the example of MIN and MAX functions

The following statement refers to the least and most extreme list prices of all products with the standard 2018 brand model:

Ex 1:

SELECT customer_id, YEAR (order_date) order_year, COUNT (order_id) order_placed FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id;

Ex 2:

SELECT customer_id, YEAR (order_date) order_year, order_status FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id;

Ex 3:

SELECT brand_name, MIN (list_price) min_price, MAX (list_price) max_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id WHERE model_year = 2018 GROUP BY brand_name ORDER BY brand_name;

SQL Worker Gathering BY - Example MIN and MAX

In this example, the WHERE provision is processed before the Proviso Gathering BY, as always.

Use of Gathering BY provision with AVG feature example()

The following statement uses the AVG() function to return the standard average brand list price for all products with the 2018 model year:

Ex 1:

SELECT brand_name, AVG (list_price) avg_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id WHERE model_year = 2018 GROUP BY brand_name ORDER BY brand_name;

Ex 2:

SELECT brand_name, AVG (list_price) avg_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id WHERE model_year = 2018 GROUP BY brand_name ORDER BY brand_name;

The following query uses The Entirety() function to get the net worth of each order:

SELECT order_id, SUM ( quantity * list_price * (1 - discount) ) net_value FROM sales.order_items GROUP BY order_id;