Try .NET /elinq/
Powered by Try .NET

SQL Server GROUPING SETS

Setup a sales summary table

Creating an ordinary table is usually not an option in real app since there might be a collision if queries are executed in parallel. The common technique is creating a temporary table per session. This is exactly what we do here:

const String SALES_SUMMARY = "#sales_summary";
DbContext.Database.Query((OrderItems i, Products p, Brands b, Categories c) =>
{
    var salesSummary = ToTable<SalesSummaryByYear>(SALES_SUMMARY);

    SELECT<SalesSummaryByYear>(b.BrandName.@as(salesSummary.Brand),
            c.CategoryName.@as(salesSummary.Category),
            p.ModelYear.@as(salesSummary.ModelYear),
            ROUND(SUM(i.Quantity * i.ListPrice * (1 - i.Discount)), 0).@as(salesSummary.Sales))
        .INTO(salesSummary);
    FROM(i).JOIN(p).ON(i.Product == p).JOIN(b).ON(p.Brand == b).JOIN(c).ON(p.Category == c);
    GROUP(BY(b.BrandName), BY(c.CategoryName), BY(p.ModelYear));
});

var query = DbContext.Set<SalesSummaryByYear>()
    .Query(() =>
    {
        var salesSummary = ToTable<SalesSummaryByYear>(SALES_SUMMARY);

        var result = SELECT(salesSummary);
        FROM(salesSummary);

        return result;
    })
    .OrderBy(c => c.Brand)
    .ThenBy(c => c.Category)
    .ThenBy(c => c.ModelYear);

foreach (var salesSummary in query.Take(3))
    Console.WriteLine((salesSummary.Brand, salesSummary.Category, salesSummary.ModelYear, salesSummary.Sales));

Run the example above and see that 2 separate queries were executed. The first creates the #sales_summary temporary table and the second uses it.

Temporary table is potentially complex. The same result can be achieved with a subquery. Also, if the same subquery is going to be used several times, it can be refactored to a separate function. Run the following and see same results as above:

private static SalesSummaryByYear GetSalesSummaryTable()
{
    return SubQuery((OrderItems i, Products p, Brands b, Categories c, SalesSummaryByYear alias) =>
    {
        var r = SELECT<SalesSummaryByYear>(b.BrandName.@as(alias.Brand),
            c.CategoryName.@as(alias.Category),
            p.ModelYear.@as(alias.ModelYear),
            ROUND(SUM(i.Quantity * i.ListPrice * (1 - i.Discount)), 0).@as(alias.Sales));
        FROM(i).JOIN(p).ON(i.Product == p).JOIN(b).ON(p.Brand == b).JOIN(c).ON(p.Category == c);
        GROUP(BY(b.BrandName), BY(c.CategoryName), BY(p.ModelYear));

        return r;
    });
}

// Don't change method name
public void SetupSalesSummaryTest()
{
    var query = DbContext.Set<SalesSummaryByYear>()
        .Query(() => SelectAll(GetSalesSummaryTable()))
        .OrderBy(ss => ss.Brand)
        .ThenBy(ss => ss.Category)
        .ThenBy(ss => ss.ModelYear);

    foreach (var salesSummary in query.Take(3))
        Console.WriteLine((salesSummary.Brand, salesSummary.Category, salesSummary.ModelYear, salesSummary.Sales));
}

GROUPING function

All the features in one example:

var query = DbContext.Set<SalesGrouping>()
                .Query((SalesGrouping alias) =>
                {
                    var salesSummary = GetSalesSummaryTable();

                    var result = SELECT<SalesGrouping>(GROUPING(salesSummary.Brand).@as(alias.GroupingBrand),
                        GROUPING(salesSummary.Category).@as(alias.GroupingCategory),
                        salesSummary.Brand.@as(alias.Brand),
                        salesSummary.Category.@as(alias.Category),
                        SUM(salesSummary.Sales).@as(alias.Sales));

                    FROM(salesSummary);
                    GROUP(BY(GROUPING_SETS(SET(salesSummary.Brand, salesSummary.Category),
                        SET(salesSummary.Brand),
                        SET(salesSummary.Category),
                        SET())));

                    return result;
                })
                .OrderBy(ss => ss.Brand)
                .ThenBy(ss => ss.Category);

foreach (var salesSummary in query.Take(3))
    Console.WriteLine((salesSummary.GroupingBrand, salesSummary.GroupingCategory, salesSummary.Brand ?? "NULL", salesSummary.Category ?? "NULL",
        salesSummary.Sales));

< BACK | HOME