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:
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;