Introduction
The GROUP BY
clause is a powerful feature in SQL Server used to group rows from a table based on specific columns. It allows you to perform aggregate functions (like COUNT, SUM, AVG, etc.) on those grouped data, enabling you to obtain valuable insights from large datasets.
Syntax
The basic syntax of the GROUP BY
clause is as follows:
sqlSELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;
Explanation
SELECT
: Specifies the columns you want to retrieve from the table.aggregate_function
: Represents the SQL aggregate function (e.g., COUNT, SUM, AVG) that you want to apply to the grouped data incolumn3
.FROM
: Specifies the table name you want to query.GROUP BY
: Indicates the columns based on which the rows will be grouped.
Example
Consider the following "Orders" table:
OrderID | CustomerID | Product | Quantity | Price |
---|---|---|---|---|
1 | 101 | Product A | 2 | 10.0 |
2 | 102 | Product B | 1 | 15.0 |
3 | 101 | Product A | 5 | 10.0 |
4 | 103 | Product C | 3 | 20.0 |
5 | 102 | Product A | 2 | 10.0 |
Scenario:
We want to know the total quantity of each product ordered.
SQL Query:
sqlSELECT Product, SUM(Quantity) AS TotalQuantity
FROM Orders
GROUP BY Product;
Output:
Product | TotalQuantity |
---|---|
Product A | 9 |
Product B | 1 |
Product C | 3 |
Explanation
In the above example, we used the GROUP BY
clause to group the data by the "Product" column. The SUM
function was used to calculate the total quantity of each product. The result is grouped by the "Product" column, and the AS
keyword allows us to assign a meaningful alias "TotalQuantity" to the aggregated result.
Conclusion
The GROUP BY
clause in SQL Server is a powerful tool to summarize and analyze data by grouping it based on specific columns. It allows you to perform aggregate functions on those grouped data, providing valuable insights for decision-making and analysis.l
0 Comments