GROUP BY - Oracle Clauses

The GROUP BY clause is used in SQL queries to group rows based on one or more columns. This clause is usually used with the SELECT statement to aggregate data and provide summary information.

Syntax

The syntax for the GROUP BY clause is as follows:

SELECT column_name1, column_name2, aggregate_function(column_name3)
FROM table_name
WHERE condition
GROUP BY column_name1, column_name2

Here, column_name1 and column_name2 are the columns on which the grouping will be done, and aggregate_function is one of the SQL aggregate functions like SUM, AVG, MAX, MIN, and COUNT.

Example

Consider a table customers that contains the following data:

id	name	city	order_amount
1	Alice	New York	1000
2	Bob	New York	2000
3	Charlie	Los Angeles	500
4	David	Chicago	1500
5	Emma	Los Angeles	750

To find the total order amount for each city, we can use the following query:

SELECT city, SUM(order_amount)
FROM customers
GROUP BY city

This will result in the following output:

city	SUM(order_amount)
New York	3000
Los Angeles	1250
Chicago	1500

Output

The output of the above query will be a table that shows the total order amount for each city.

Explanation

In the above example, we have used the SUM aggregate function to find the total order amount for each city. We have grouped the data by the city column using the GROUP BY clause. This results in the data being grouped by the city column, and the SUM aggregate function is applied to the order_amount column for each group.

Use

The GROUP BY clause is commonly used in SQL queries to group data based on one or more columns. It is particularly useful when dealing with large datasets and when summary information is required.

Important Points

• The GROUP BY clause is used to group rows based on one or more columns.
• It is used with SQL aggregate functions to provide summary information.
• The GROUP BY clause is commonly used when dealing with large datasets.

Summary

In summary, the GROUP BY clause is an important feature of SQL that allows data to be grouped based on one or more columns. It is often used with SQL aggregate functions to provide summary information. The GROUP BY clause is particularly useful when dealing with large datasets and when summary information is required.