Explain the purpose of the GROUP BY clause in SQL and provide an example.

Question

Aryan Kumar · Answer

In SQL, the GROUP BY clause is used to group rows based on one or more columns and perform aggregate functions on each group. Its purpose is to summarize or aggregate data based on specific criteria and provide meaningful insights.

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

SELECT column1, column2, ..., aggregate_function(column)
FROM table
GROUP BY column1, column2, ...;

Here's how it works:

1. The SELECT statement retrieves the specified columns and can also include aggregate functions such as SUM, AVG, COUNT, MAX, MIN, etc.
2. The GROUP BY clause specifies the columns that will be used for grouping the rows.
3. The result set is divided into groups based on the unique combinations of values in the specified columns.
4. The aggregate functions are then applied to each group, calculating the desired values for each group.
5. The result set will include one row for each group, with the aggregated values.

Here's an example to illustrate the usage of the GROUP BY clause:

Consider a table named "orders" with the following columns: order_id, customer_id, product_id, order_date, and quantity.

To find the total quantity of products ordered by each customer, we can use the GROUP BY clause as follows:

SELECT customer_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_id;

In this example, the result set will contain two columns: customer_id and total_quantity. The rows will be grouped based on the customer_id column, and the SUM function will calculate the total quantity of products ordered for each customer.

The output may look like this:

customer_id | total_quantity
---------------------------
  101      |      25
  102      |      18
  103      |      30

This result shows the customer IDs and the corresponding total quantities of products ordered by each customer.

The GROUP BY clause is powerful for performing data aggregation and summarization operations in SQL, allowing you to analyze data at a higher level of granularity. It is often used in conjunction with aggregate functions to generate meaningful reports and insights from large datasets.

forum

Explain the purpose of the GROUP BY clause in SQL and provide an example.

Can you answer this question?

1 Answers