Can you explain how the GROUP BY operator works in SQL and what it is used for?

The GROUP BY operator in SQL is used to group rows that have the same values in specified columns. This allows for performing aggregate functions (such as SUM, COUNT, AVG, MAX, MIN) on each group of data.

For example, if you have a sales table with columns product_id and amount, and you want to find the total sales for each product, you can write:

SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Here, GROUP BY product_id will combine all records with the same product_id into one group, and the sum of amount will be calculated for each group.

Thus, GROUP BY helps to aggregate data by categories or keys, which is useful for reports and analysis.