GROUPING_ID SQL function

1. Usage of GROUPING_ID with ROLLUP to show total freight country and city wise.

SQL Server Query 1

            
SELECT  

    c.Country,  

    c.City,  

    SUM(o.Freight) AS TotalFreight,  

    GROUPING(c.Country) AS GroupingCountry,  

    GROUPING(c.City) AS GroupingCity,  

    GROUPING_ID(c.Country, c.City) AS GroupingID  

FROM Customers c  

JOIN Orders o ON c.CustomerID = o.CustomerID  

GROUP BY ROLLUP (c.Country, c.City)  

ORDER BY c.Country, c.City;  

            
        

Create SQL query with SqlQueryBuilder 1

            
.Select()  

.Columns("c.Country", "c.City")  

.Column(new SUM(new Column("o.Freight")), "TotalFreight")  

.Column(new GROUPING(new Column("c.Country")), "GroupingCountry")  

.Column(new GROUPING(new Column("c.City")), "GroupingCity")  

.Column(new GROUPING_ID(new Column("c.Country"), new Column("c.City")), "GroupingID")  

.From("Customers", "c")  

.Join(new List<IJoin>()  

{  

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

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

})  

.GroupBy(new GroupBy(new Column("c.Country"), new Column("c.City")).WithRollUp())  

.OrderBy(new OrderBy()  

    .SetColumnAscending("c.Country")  

    .SetColumnAscending("c.City"))  

.Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT c.Country,
       c.City,
       SUM(o.Freight) AS TotalFreight,
       GROUPING(c.Country) AS GroupingCountry,
       GROUPING(c.City) AS GroupingCity,
       GROUPING_ID(c.Country, c.City) AS GroupingID
FROM Customers AS c
     INNER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
GROUP BY ROLLUP(c.Country, c.City)
ORDER BY c.Country ASC, c.City ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of GROUPING_ID with GROUPING SETS

SQL Server Query 2

            
SELECT  

    c.Country,  

    c.City,  

    s.CompanyName AS Shipper,  

    SUM(o.Freight) AS TotalFreight,  

    GROUPING(c.Country) AS GroupingCountry,  

    GROUPING(c.City) AS GroupingCity,  

    GROUPING(s.CompanyName) AS GroupingShipper,  

    GROUPING_ID(c.Country, c.City, s.CompanyName) AS GroupingID  

FROM Customers c  

JOIN Orders o ON c.CustomerID = o.CustomerID  

JOIN Shippers s ON o.ShipVia = s.ShipperID  

GROUP BY GROUPING SETS (  

    (c.Country, c.City),  

    (c.Country, s.CompanyName),  

    (s.CompanyName),  

    () -- Grand total  

)  

ORDER BY c.Country, c.City, s.CompanyName;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Columns("c.Country", "c.City")  

    .Column("s.CompanyName","Shipper")  

    .Column(new SUM(new Column("o.Freight")), "TotalFreight")  

    .Column(new GROUPING(new Column("c.Country")), "CountryGrouping")  

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

    .Column(new GROUPING(new Column("s.CompanyName")), "GroupingShipper")  

    .Column(new GROUPING_ID(new Column("c.Country"), new Column("c.City"), new Column("s.CompanyName")), "GroupingID")  

    .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("Shippers", "s")  

            .On(new Column("o.ShipVia").Equale(new Column("s.ShipperID")))  

    })  

    .GroupBy(new GroupBy().WithGroupingSets(new List<List<string>>()   

    {  

        new List<string> { "c.Country", "c.City" },  

        new List<string> { "c.Country", "s.CompanyName" },  

        new List<string> { "s.CompanyName" },  

        new List<string>()  

    }))  

    .OrderBy(new OrderBy()  

        .SetColumnAscending("c.Country")  

        .SetColumnAscending("c.City")  

        .SetColumnAscending("s.CompanyName"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT c.Country,
       c.City,
       s.CompanyName AS Shipper,
       SUM(o.Freight) AS TotalFreight,
       GROUPING(c.Country) AS CountryGrouping,
       GROUPING(c.City) AS CustomerGrouping,
       GROUPING(s.CompanyName) AS GroupingShipper,
       GROUPING_ID(c.Country, c.City, s.CompanyName) AS GroupingID
FROM Customers AS c
     INNER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
     INNER JOIN
     Shippers AS s
     ON o.ShipVia = s.ShipperID
GROUP BY GROUPING SETS((c.Country, c.City), (c.Country, s.CompanyName), (s.CompanyName), ())
ORDER BY c.Country ASC, c.City ASC, s.CompanyName ASC;


            
        

Parameters (If used)

Query Results 2:

3. Usage of GROUPING_ID to Filter Aggregation Levels

SQL Server Query 3

            
SELECT  

    c.Country,  

    c.City,  

    SUM(o.Freight) AS TotalFreight  

FROM Customers c  

JOIN Orders o ON c.CustomerID = o.CustomerID  

GROUP BY ROLLUP (c.Country, c.City)  

HAVING GROUPING_ID(c.Country, c.City) = 1 -- Filter for subtotals by Country  

ORDER BY c.Country;  

            
        

Create SQL query with SqlQueryBuilder 3

            
.Select()  

  .Columns("c.Country", "c.City")  

  .Column(new SUM(new Column("o.Freight")), "TotalFreight")  

.From("Customers", "c")  

.Join(new List<IJoin>()  

{  

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

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

})  

.GroupBy(new GroupBy("c.Country", "c.City").WithRollUp())  

.Having(new Having(new GROUPING_ID(new Column("c.Country"), new Column("c.City")).Equale(1)))  

.OrderBy(new OrderBy().SetColumnAscending("c.Country"))  

.Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT c.Country,
       c.City,
       SUM(o.Freight) AS TotalFreight
FROM Customers AS c
     INNER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
GROUP BY ROLLUP(c.Country, c.City)
HAVING GROUPING_ID(c.Country, c.City) = @pMAIN_2507200130335603160
ORDER BY c.Country ASC;


            
        

Parameters (If used)

Query Results 3:

4. Usage of GROUPING_ID with CASE Statements for Clarity

SQL Server Query 4

            
SELECT  

    CASE GROUPING_ID(c.Country, c.City)  

        WHEN 0 THEN 'Total for City'  

        WHEN 1 THEN 'Subtotal for Country'  

        WHEN 3 THEN 'Grand Total'  

        ELSE 'Unknown'  

    END AS AggregationLevel,  

    c.Country,  

    c.City,  

    SUM(o.Freight) AS TotalFreight  

FROM Customers c  

JOIN Orders o ON c.CustomerID = o.CustomerID  

GROUP BY ROLLUP (c.Country, c.City)  

ORDER BY GROUPING_ID(c.Country, c.City), c.Country, c.City;  

            
        

Create SQL query with SqlQueryBuilder 4

            
var (sql4, parameters4) = new SqlQueryBuilder()  

    .Select()  

        .Column(new CASE(new GROUPING_ID(new Column("c.Country"), new Column("c.City")))  

            .When(0).Then("Total for City")  

            .When(1).Then("Subtotal for Country")  

            .When(3).Then("Grand Total")  

            .Else("Unknown")  

        , "AggregationLevel")  

    .Columns("c.Country", "c.City")  

    .Column(new SUM(new Column("o.Freight")), "TotalFreight")  

    .From("Customers", "c")  

    .Join(new List<IJoin>()  

    {  

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

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

    })  

    .GroupBy(new GroupBy("c.Country", "c.City").WithRollUp())  

    .OrderBy(new OrderBy().Set(new GROUPING_ID(new Column("c.Country"), new Column("c.City")))  

        .SetColumnAscending("c.Country")  

        .SetColumnAscending("c.City"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT CASE GROUPING_ID(c.Country, c.City) WHEN @pMAIN_2507200130335731180 THEN @pMAIN_2507200130335731181 WHEN @pMAIN_2507200130335731182 THEN @pMAIN_2507200130335731183 WHEN @pMAIN_2507200130335731184 THEN @pMAIN_2507200130335731185 ELSE @pMAIN_2507200130335731186 END AS AggregationLevel,
       c.Country,
       c.City,
       SUM(o.Freight) AS TotalFreight
FROM Customers AS c
     INNER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
GROUP BY ROLLUP(c.Country, c.City)
ORDER BY GROUPING_ID(c.Country, c.City) ASC, c.Country ASC, c.City ASC;


            
        

Parameters (If used)

Query Results 4: