Lam Bùi

Posted on Jun 4

T-SQL on Microsoft Fabric - Episode 2: Grouping and Summarizing Data with GROUP BY and Aggregate Functions

#microsoft #sql #fabric #dataengineering

Learning Goals

In this lesson, you will learn how to:

Understand aggregate functions in T-SQL
Use SUM() to calculate totals
Use COUNT() to count rows and distinct values
Use AVG() to calculate averages
Use MIN() and MAX() to find the lowest and highest values
Group data with GROUP BY
Filter grouped results with HAVING

These are some of the most frequently used SQL features in Data Warehouses, Power BI models, and Microsoft Fabric.

1. Prepare the Data

Create the schema:

CREATE SCHEMA sales;

Create the table:

CREATE TABLE sales.Orders
(
    OrderID         INT,
    CustomerID      INT,
    ProductName     VARCHAR(100),
    OrderDate       DATE,
    Amount          DECIMAL(10,2)
);

Insert sample data:

INSERT INTO sales.Orders
VALUES
(101,1,'Laptop','2026-01-01',1200),
(102,1,'Phone','2026-01-05',800),
(103,2,'Laptop','2026-01-10',1500),
(104,2,'Tablet','2026-01-15',600),
(105,3,'Phone','2026-01-20',900),
(106,3,'Laptop','2026-01-25',1800),
(107,4,'Tablet','2026-02-01',700),
(108,5,'Laptop','2026-02-05',2500);

2. SUM()

Use SUM() to calculate a total.

Total revenue:

SELECT SUM(Amount) AS TotalRevenue
FROM sales.Orders;

3. COUNT()

Use COUNT() to count rows.

Total number of orders:

SELECT COUNT(*) AS TotalOrders
FROM sales.Orders;

Count how many customers have placed orders:

SELECT COUNT(DISTINCT CustomerID) AS TotalCustomers
FROM sales.Orders;

4. AVG()

Use AVG() to calculate the average value.

SELECT AVG(Amount) AS AverageOrderValue
FROM sales.Orders;

5. MIN()

Find the smallest order value:

SELECT MIN(Amount) AS SmallestOrder
FROM sales.Orders;

6. MAX()

Find the largest order value:

SELECT MAX(Amount) AS LargestOrder
FROM sales.Orders;

7. GROUP BY

GROUP BY helps you organize rows into groups before applying aggregate functions.

Revenue by product:

SELECT ProductName,
       SUM(Amount) AS Revenue
FROM sales.Orders
GROUP BY ProductName;

Order count by product:

SELECT ProductName,
       COUNT(*) AS OrderCount
FROM sales.Orders
GROUP BY ProductName;

Average order value by product:

SELECT ProductName,
       AVG(Amount) AS AvgRevenue
FROM sales.Orders
GROUP BY ProductName;

8. HAVING

HAVING is used to filter grouped data after GROUP BY has been applied.

Show only products with revenue greater than 2000:

SELECT ProductName,
       SUM(Amount) AS Revenue
FROM sales.Orders
GROUP BY ProductName
HAVING SUM(Amount) > 2000;

9. WHERE vs HAVING

Both WHERE and HAVING filter data, but they are applied at different stages of the query.

WHERE

WHERE filters rows before grouping.

SELECT *
FROM sales.Orders
WHERE Amount > 1000;

HAVING

HAVING filters grouped results after aggregation.

SELECT ProductName,
       SUM(Amount) AS Revenue
FROM sales.Orders
GROUP BY ProductName
HAVING SUM(Amount) > 2000;

10. Real-World Fabric Warehouse Example

Build a product revenue summary for reporting or dashboards:

SELECT ProductName,
       COUNT(*) AS Orders,
       SUM(Amount) AS Revenue,
       AVG(Amount) AS AvgOrderValue,
       MIN(Amount) AS LowestOrder,
       MAX(Amount) AS HighestOrder
FROM sales.Orders
GROUP BY ProductName
ORDER BY Revenue DESC;

DEV Community