SQL GROUP BY clause

The GROUP BY clause in SQL is used to group rows that have the same values in certain columns into summary rows. This allows you to perform aggregate calculations on the grouped data, such as counting the number of occurrences of each value or calculating the sum or average of values in a column. The GROUP BY clause is typically used in combination with aggregate functions, such as SUM, AVG, MIN, MAX, and COUNT.

The syntax for using the GROUP BY clause is as follows:

1SELECT column1, aggregate_function(column2), ...
2FROM table_name
3GROUP BY column1;

For example, consider a table named sales with the following columns: id, date, product, quantity, price. If you want to retrieve the total sales for each product, you could use the following query:

1SELECT product, SUM(quantity * price) AS total_sales
2FROM sales
3GROUP BY product;

In this example, the query calculates the total sales for each product by multiplying the quantity and price for each sale and then using the SUM aggregate function to add up the total sales for each product. The result set would show one row for each product, with the product name and the total sales for that product.

It is also possible to group the data based on multiple columns. For example, if you wanted to retrieve the total sales for each product, grouped by the date on which the sale was made, you could use the following query:

1SELECT date, product, SUM(quantity * price) AS total_sales
2FROM sales
3GROUP BY date, product;

Sort group by value total_sales using order by

1SELECT date, product, SUM(quantity * price) AS total_sales
2FROM sales
3GROUP BY date, product
4ORDER BY total_sales DESC;