Using Group By operators in a Fabric warehouse.txt

-- Create Customer table
CREATE TABLE Customer (
    CustomerID INT,
    Name VARCHAR(100)
);

-- Create FoodOrder table
CREATE TABLE FoodOrder (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE,
    FoodItem VARCHAR(100),
    Quantity INT
);

-- Insert Customers
INSERT INTO Customer (CustomerID, Name)
VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Insert Orders
INSERT INTO FoodOrder (OrderID, CustomerID, OrderDate, FoodItem, Quantity)
VALUES
(1, 1, '2023-01-10', 'Pizza', 2),
(2, 1, '2023-03-15', 'Burger', 1),
(3, 2, '2023-05-12', 'Pizza', 3),
(4, 2, '2023-07-20', 'Salad', 1),
(5, 3, '2024-02-05', 'Burger', 4),
(6, 3, '2024-04-18', 'Pizza', 2),
(7, 1, '2024-06-11', 'Salad', 1);


--Query 1
SELECT YEAR(FO.OrderDate) AS OrderYear, FO.FoodItem,
       SUM(FO.Quantity) AS TotalQuantity
FROM FoodOrder FO INNER JOIN Customer C
    ON FO.CustomerID = C.CustomerID
GROUP BY ROLLUP(YEAR(FO.OrderDate), FO.FoodItem)
ORDER BY OrderYear, FoodItem;


--Query 2
SELECT YEAR(FO.OrderDate) AS OrderYear, FO.FoodItem,
       SUM(FO.Quantity) AS TotalQuantity
FROM FoodOrder FO INNER JOIN Customer C
    ON FO.CustomerID = C.CustomerID
GROUP BY ROLLUP(FO.FoodItem, YEAR(FO.OrderDate))
ORDER BY OrderYear, FoodItem;


--Query 3
SELECT YEAR(FO.OrderDate) AS OrderYear, FO.FoodItem, SUM(FO.Quantity) AS TotalQuantity
FROM FoodOrder FO INNER JOIN Customer C
    ON FO.CustomerID = C.CustomerID
GROUP BY GROUPING SETS (
    (YEAR(FO.OrderDate), FO.FoodItem),        
    (FO.FoodItem)               
)
ORDER BY OrderYear, FoodItem;