GROUPING SQL function

1. Usage of GROUPING to show order total based on shipped country

SQL Server Query 1

            
-- Analyze order counts by customer and customer demographics  

SELECT  

    c.CustomerID,  

    c.CompanyName,  

    c.ContactName,  

    c.Country,  

    COUNT_BIG(o.OrderID) AS TotalOrders,  -- Use COUNT_BIG here  

    AVG(CAST(od.Quantity * od.UnitPrice * (1 - od.Discount) AS DECIMAL(18, 2))) AS AverageOrderValue,  

    MAX(CAST(od.Quantity * od.UnitPrice * (1 - od.Discount) AS DECIMAL(18, 2))) AS MaxOrderValue,  

    MIN(CAST(od.Quantity * od.UnitPrice * (1 - od.Discount) AS DECIMAL(18, 2))) AS MinOrderValue  

FROM Customers AS c  

JOIN Orders AS o ON c.CustomerID = o.CustomerID  

JOIN [Order Details] AS od ON o.OrderID = od.OrderID  

GROUP BY c.CustomerID, c.CompanyName, c.ContactName, c.Country  

ORDER BY TotalOrders DESC;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Columns("c.CustomerID","c.CompanyName","c.ContactName","c.Country")  

    .Column(new COUNT_BIG(new Column("o.OrderID")), "TotalOrders")  

    .Column(new AVG(new CAST(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice").MULTIPLY()  

            .StartBracket(1).SUBTRACT("od.Discount").EndBracket(), SqlDataType.DECIMAL, new Tuple<int,int>(18,2)))  

    , "AverageOrderValue")  

    .Column(new MAX(new CAST(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice").MULTIPLY()  

            .StartBracket(1).SUBTRACT("od.Discount").EndBracket(), SqlDataType.DECIMAL, new Tuple<int, int>(18, 2)))  

    , "MaxOrderValue")  

    .Column(new MIN(new CAST(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice").MULTIPLY()  

            .StartBracket(1).SUBTRACT("od.Discount").EndBracket(), SqlDataType.DECIMAL, new Tuple<int, int>(18, 2)))  

    , "MinOrderValue")  

    .From("Customers", "c")  

    .Join(new List<IJoin>()   

    {  

        new INNERJOIN().TableName("Orders", "o")  

        .On(new Column("c.CustomerID").Equale(new Column("o.CustomerID"))),  

        new INNERJOIN().TableName("[Order Details]", "od")  

        .On(new Column("o.OrderID").Equale(new Column("od.OrderID")))  

    })  

    .GroupBy(new GroupBy("c.CustomerID","c.CompanyName","c.ContactName","c.Country"))  

    .OrderBy(new OrderBy()  

        .SetColumnDescending("TotalOrders"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH OrderDetailsWithGrouping
AS (SELECT ShipCountry AS ShipCountry,
           SUM(UnitPrice * Quantity * (@pMAIN_2507200140484164550 - Discount)) AS OrderTotal,
           GROUPING(ShipCountry) AS GroupingLevel
    FROM [Order Details]
         INNER JOIN
         Orders
         ON [Order Details].OrderID = Orders.OrderID
    GROUP BY ROLLUP(ShipCountry))
SELECT ShipCountry,
       OrderTotal,
       CASE WHEN GroupingLevel = @pMAIN_2507200140484164551 THEN @pMAIN_2507200140484164552 WHEN GroupingLevel = @pMAIN_2507200140484164553 THEN @pMAIN_2507200140484164554 ELSE @pMAIN_2507200140484164555 END AS GroupingSource
FROM OrderDetailsWithGrouping
WHERE GroupingLevel IN (@pMAIN_2507200140484164556, @pMAIN_2507200140484164557)
ORDER BY GroupingLevel ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of GROUPING to Total sales with ROLLUP

SQL Server Query 2

            
SELECT  

    o.ShipCountry,  

    c.CustomerID,  

    SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalSales,  

    GROUPING(o.ShipCountry) AS CountryGrouping,  

    GROUPING(c.CustomerID) AS CustomerGrouping  

FROM Orders AS o  

JOIN Customers AS c ON o.CustomerID = c.CustomerID  

JOIN [Order Details] AS od ON o.OrderID = od.OrderID  

GROUP BY ROLLUP (o.ShipCountry, c.CustomerID)  -- Using ROLLUP  

ORDER BY o.ShipCountry, c.CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Columns("o.ShipCountry","c.CustomerID")  

    .Column(new SUM(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice").MULTIPLY()  

        .StartBracket(1).SUBTRACT("od.Discount").EndBracket()), "TotalSales")  

    .Column(new GROUPING(new Column("o.ShipCountry")), "CountryGrouping")  

    .Column(new GROUPING(new Column("c.CustomerID")), "CustomerGrouping")  

    .From("Orders","o")  

    .Join(new List<IJoin>()   

    {  

        new INNERJOIN().TableName("Customers", "c")  

            .On(new Column("o.CustomerID").Equale(new Column("c.CustomerID"))),  

        new INNERJOIN().TableName("[Order Details]", "od")  

            .On(new Column("o.OrderID").Equale(new Column("od.OrderID")))  

    })  

    .GroupBy(new GroupBy("o.ShipCountry","c.CustomerID").WithRollUp())  

    .OrderBy(new OrderBy()  

        .SetColumnAscending("o.ShipCountry")  

        .SetColumnAscending("c.CustomerID"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT o.ShipCountry,
       c.CustomerID,
       SUM(od.Quantity * od.UnitPrice * (@pMAIN_2507200140484380360 - od.Discount)) AS TotalSales,
       GROUPING(o.ShipCountry) AS CountryGrouping,
       GROUPING(c.CustomerID) AS CustomerGrouping
FROM Orders AS o
     INNER JOIN
     Customers AS c
     ON o.CustomerID = c.CustomerID
     INNER JOIN
     [Order Details] AS od
     ON o.OrderID = od.OrderID
GROUP BY ROLLUP(o.ShipCountry, c.CustomerID)
ORDER BY o.ShipCountry ASC, c.CustomerID ASC;


            
        

Parameters (If used)

Query Results 2:

2. Usage of GROUPING to Total sales with CUBE

SQL Server Query 3

            
SELECT  

    o.ShipCountry,  

    c.CustomerID,  

    SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalSales,  

    GROUPING(o.ShipCountry) AS CountryGrouping,  

    GROUPING(c.CustomerID) AS CustomerGrouping  

FROM Orders AS o  

JOIN Customers AS c ON o.CustomerID = c.CustomerID  

JOIN [Order Details] AS od ON o.OrderID = od.OrderID  

GROUP BY ROLLUP (o.ShipCountry, c.CustomerID)  -- Using CUBE  

ORDER BY o.ShipCountry, c.CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .Select()  

    .Columns("o.ShipCountry","c.CustomerID")  

    .Column(new SUM(new ColumnArithmatic("od.Quantity").MULTIPLY("od.UnitPrice").MULTIPLY()  

        .StartBracket(1).SUBTRACT("od.Discount").EndBracket()), "TotalSales")  

    .Column(new GROUPING(new Column("o.ShipCountry")), "CountryGrouping")  

    .Column(new GROUPING(new Column("c.CustomerID")), "CustomerGrouping")  

    .From("Orders","o")  

    .Join(new List<IJoin>()   

    {  

        new INNERJOIN().TableName("Customers", "c")  

            .On(new Column("o.CustomerID").Equale(new Column("c.CustomerID"))),  

        new INNERJOIN().TableName("[Order Details]", "od")  

            .On(new Column("o.OrderID").Equale(new Column("od.OrderID")))  

    })  

    .GroupBy(new GroupBy("o.ShipCountry","c.CustomerID").WithCube())  

    .OrderBy(new OrderBy()  

        .SetColumnAscending("o.ShipCountry")  

        .SetColumnAscending("c.CustomerID"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT o.ShipCountry,
       c.CustomerID,
       SUM(od.Quantity * od.UnitPrice * (@pMAIN_2507200140484617970 - od.Discount)) AS TotalSales,
       GROUPING(o.ShipCountry) AS CountryGrouping,
       GROUPING(c.CustomerID) AS CustomerGrouping
FROM Orders AS o
     INNER JOIN
     Customers AS c
     ON o.CustomerID = c.CustomerID
     INNER JOIN
     [Order Details] AS od
     ON o.OrderID = od.OrderID
GROUP BY CUBE(o.ShipCountry, c.CustomerID)
ORDER BY o.ShipCountry ASC, c.CustomerID ASC;


            
        

Parameters (If used)

Query Results 3: