SUM SQL function

1. Usage of SUM to format the total order amount

SQL Server Query 1

            
SELECT TOP 10  

    o.OrderID,  

    c.CompanyName,  

    'Total: $' +  

    CASE  

        WHEN SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) > 1000  

            THEN '**' + STR(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)), 10, 2)  

        ELSE STR(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)), 10, 2)  

    END AS FormattedTotalAmount  

FROM  

    Orders o  

JOIN  

    Customers c ON o.CustomerID = c.CustomerID  

JOIN  

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

GROUP BY  

    o.OrderID,  

    c.CompanyName  

ORDER BY  

    o.OrderID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select().Top(10)  

    .Columns("o.OrderID", "c.CompanyName")  

    .Column(new CONCAT(  

        "Total: $",  

        new CASE()  

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

                .StartBracket(1).SUBTRACT("od.Discount").EndBracket()).GreaterThan(100))  

            .Then(new CONCAT("**", new STR(new SUM(  

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

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

            ), 10, 2)))  

            .Else(new STR(  

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

                        .StartBracket(1).SUBTRACT("od.Discount").EndBracket()), 10, 2))  

    ), "FormattedTotalAmount")  

    .From("Orders", "o")  

    .Join(new List<IJoin>()  

    {  

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

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

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

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

    })  

    .GroupBy(new GroupBy("o.OrderID", "c.CompanyName"))  

    .OrderBy(new OrderBy().SetColumnAscending("o.OrderID"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 10 o.OrderID,
              c.CompanyName,
              CONCAT(@pMAIN_2507200147313026240, CASE WHEN SUM(od.UnitPrice * od.Quantity * (@pMAIN_2507200147313026241 - od.Discount)) > @pMAIN_2507200147313026242 THEN CONCAT(@pMAIN_2507200147313026243, STR(SUM(od.UnitPrice * od.Quantity * (@pMAIN_2507200147313026244 - od.Discount)), @pMAIN_2507200147313026245, @pMAIN_2507200147313026246)) ELSE STR(SUM(od.UnitPrice * od.Quantity * (@pMAIN_2507200147313026247 - od.Discount)), @pMAIN_2507200147313026248, @pMAIN_2507200147313026249) END) AS FormattedTotalAmount
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 o.OrderID, c.CompanyName
ORDER BY o.OrderID ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of SUM to find sales trend angle

SQL Server Query 2

            
WITH MonthlyCategorySales AS (  

    SELECT  

        c.CategoryName,  

        DATEPART(month, o.OrderDate) AS SaleMonth,  

        SUM(od.Quantity * od.UnitPrice) AS MonthlySales  

    FROM Categories c  

    JOIN Products p ON c.CategoryID = p.CategoryID  

    JOIN [Order Details] od ON p.ProductID = od.ProductID  

    JOIN Orders o ON od.OrderID = o.OrderID  

    WHERE o.OrderDate BETWEEN '1996-01-01' AND '1996-12-31'  -- Limit to one year for simplicity  

    GROUP BY c.CategoryName, DATEPART(month, o.OrderDate)  

),  

CategoryTotalSales AS (  

    SELECT  

        CategoryName,  

        SUM(MonthlySales) AS TotalSales  

    FROM MonthlyCategorySales  

    GROUP BY CategoryName  

),  

NormalizedSales AS (  

    SELECT  

        mcs.CategoryName,  

        mcs.SaleMonth,  

        -- Simplified normalization:  (MonthlySales / TotalSales) * 2 -1.  This isn't robust, but good for example  

        CAST(mcs.MonthlySales AS FLOAT) / cts.TotalSales  AS NormalizedSales  

    FROM MonthlyCategorySales mcs  

    JOIN CategoryTotalSales cts ON mcs.CategoryName = cts.CategoryName  

),  

SalesTrendAngle AS (  

    SELECT  

        CategoryName,  

        --  Calculate ASIN of the normalized sales.  

        ASIN(AVG(NormalizedSales)) AS TrendAngleInRadians,  

        DEGREES(ASIN(AVG(NormalizedSales))) AS TrendAngleInDegrees  

    FROM NormalizedSales  

    GROUP BY CategoryName  

)  

SELECT  

    CategoryName,  

    TrendAngleInRadians,  

    TrendAngleInDegrees  

FROM SalesTrendAngle  

ORDER BY TrendAngleInDegrees;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .WithCTETable(new Table("MonthlyCategorySales"), new SqlQueryBuilder()  

        .Select()  

        .Column("c.CategoryName", "CategoryName")  

        .Column(new DATEPART(SqlDateInterval.month, new Column("o.OrderDate")), "SaleMonth")  

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

        .From("Categories", "c")  

        .Join(new List<IJoin>()  

        {  

            new INNERJOIN().TableName(new Table("Products","p"))  

                .On(new Column("c.CategoryID").Equale(new Column("p.CategoryID"))),  

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

                .On(new Column("p.ProductID").Equale(new Column("od.ProductID"))),  

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

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

        })  

        .Where(new Where(new BETWEEN(new Column("o.OrderDate"), "1996-01-01", "1996-12-31")))  

        .GroupBy(new GroupBy(new Column("c.CategoryName"), new DATEPART(SqlDateInterval.month, new Column("o.OrderDate"))))  

    )  

    .WithCTETable(new Table("CategoryTotalSales"), new SqlQueryBuilder()  

        .Select()  

        .Column("CategoryName", "CategoryName")  

        .Column(new SUM(new Column("MonthlySales")), "TotalSales")  

        .From("MonthlyCategorySales")  

        .GroupBy(new GroupBy(new Column("CategoryName")))  

    )  

    .WithCTETable(new Table("NormalizedSales"), new SqlQueryBuilder()  

        .Select()  

        .Columns("mcs.CategoryName", "mcs.SaleMonth")  

        .Column(new ColumnArithmatic(new CAST(new Column("mcs.MonthlySales"), SqlDataType.FLOAT)).DIVIDE("cts.TotalSales"), "NormalizedSales")  

        .From("MonthlyCategorySales", "mcs")  

        .Join(new List<IJoin>()  

        {  

            new INNERJOIN().TableName(new Table("CategoryTotalSales","cts"))  

                .On(new Column("mcs.CategoryName").Equale(new Column("cts.CategoryName")))  

        })  

    )  

    .WithCTETable(new Table("SalesTrendAngle"), new SqlQueryBuilder()  

        .Select()  

        .Column("CategoryName", "CategoryName")  

        .Column(new ASIN(new AVG(new Column("NormalizedSales"))), "TrendAngleInRadians")  

        .Column(new DEGREES(new ASIN(new AVG(new Column("NormalizedSales")))), "TrendAngleInDegrees")  

        .From("NormalizedSales")  

        .GroupBy(new GroupBy("CategoryName"))  

    )  

    .Select()  

    .Columns("CategoryName", "TrendAngleInRadians", "TrendAngleInDegrees")  

    .From("SalesTrendAngle")  

    .OrderBy(new OrderBy().SetColumnAscending("TrendAngleInDegrees"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
WITH MonthlyCategorySales
AS (SELECT c.CategoryName AS CategoryName,
           DATEPART(month, o.OrderDate) AS SaleMonth,
           SUM(od.Quantity * od.UnitPrice) AS MonthlySales
    FROM Categories AS c
         INNER JOIN
         Products AS p
         ON c.CategoryID = p.CategoryID
         INNER JOIN
         [Order Details] AS od
         ON p.ProductID = od.ProductID
         INNER JOIN
         Orders AS o
         ON od.OrderID = o.OrderID
    WHERE o.OrderDate BETWEEN @pMAIN_2507200147313203940 AND @pMAIN_2507200147313203941
    GROUP BY c.CategoryName, DATEPART(month, o.OrderDate)),
 CategoryTotalSales
AS (SELECT CategoryName AS CategoryName,
           SUM(MonthlySales) AS TotalSales
    FROM MonthlyCategorySales
    GROUP BY CategoryName),
 NormalizedSales
AS (SELECT mcs.CategoryName,
           mcs.SaleMonth,
           CAST (mcs.MonthlySales AS FLOAT) / cts.TotalSales AS NormalizedSales
    FROM MonthlyCategorySales AS mcs
         INNER JOIN
         CategoryTotalSales AS cts
         ON mcs.CategoryName = cts.CategoryName),
 SalesTrendAngle
AS (SELECT CategoryName AS CategoryName,
           ASIN(AVG(NormalizedSales)) AS TrendAngleInRadians,
           DEGREES(ASIN(AVG(NormalizedSales))) AS TrendAngleInDegrees
    FROM NormalizedSales
    GROUP BY CategoryName)
SELECT CategoryName,
       TrendAngleInRadians,
       TrendAngleInDegrees
FROM SalesTrendAngle
ORDER BY TrendAngleInDegrees ASC;


            
        

Parameters (If used)

Query Results 2:

3. Usage of SUM to find shipping containers

SQL Server Query 3

            
WITH OrderUnits AS (  

    SELECT  

        o.OrderID,  

        c.CompanyName AS CustomerName,  

        SUM(od.Quantity) AS TotalUnits  

    FROM Orders o  

    JOIN Customers c ON o.CustomerID = c.CustomerID  

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

    GROUP BY o.OrderID, c.CompanyName  

)  

SELECT  

    OrderID,  

    CustomerName,  

    TotalUnits,  

    CEILING(CAST(TotalUnits AS FLOAT) / 250) AS NumberOfContainers  

FROM OrderUnits  

ORDER BY OrderID;  

            
        

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .WithCTETable(new Table("OrderUnits"), new SqlQueryBuilder()  

        .Select()  

        .Column("o.OrderID", "OrderID")  

        .Column("c.CompanyName", "CustomerName")  

        .Column(new SUM(new Column("od.Quantity")), "TotalUnits")  

        .From("Orders", "o")  

        .Join(new List<IJoin>()  

        {  

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

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

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

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

        })  

        .GroupBy(new GroupBy(new Column("o.OrderID"), new Column("c.CompanyName")))  

    )  

    .Select()  

    .Columns("OrderID", "CustomerName", "TotalUnits")  

    .Column(new ColumnArithmatic(new CEILING(new CAST(new Column("TotalUnits"), SqlDataType.FLOAT)))  

        .DIVIDE(250), "NumberOfContainers")  

    .From("OrderUnits")  

    .OrderBy(new OrderBy().SetColumnAscending("OrderID"))  

    .Build()  

            
        

Query build by SqlQueryBuilder 3

            
WITH OrderUnits
AS (SELECT o.OrderID AS OrderID,
           c.CompanyName AS CustomerName,
           SUM(od.Quantity) AS TotalUnits
    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 o.OrderID, c.CompanyName)
SELECT OrderID,
       CustomerName,
       TotalUnits,
       CEILING(CAST (TotalUnits AS FLOAT)) / @pMAIN_2507200147313556190 AS NumberOfContainers
FROM OrderUnits
ORDER BY OrderID ASC;


            
        

Parameters (If used)

Query Results 3:

4. Usage of SUM to find cyclic sales pattern

SQL Server Query 4

            
WITH Months AS (  

    SELECT 1 AS Month  

    UNION ALL  

    SELECT Month + 1  

    FROM Months  

    WHERE Month < 12  

),  

MonthlySales AS (  

    SELECT  

        DATEPART(month, o.OrderDate) AS SaleMonth,  

        SUM(od.Quantity * od.UnitPrice) AS TotalSales  

    FROM Orders o  

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

    WHERE YEAR(o.OrderDate) = 1996  --  Limit to a single year for simplicity  

    GROUP BY DATEPART(month, o.OrderDate)  

),  

SalesAnalysis AS (  

    SELECT  

        m.Month AS SaleMonth,  

        COALESCE(ms.TotalSales, 0) AS ActualSales, -- Use COALESCE to handle months with no sales  

        -- Calculate a cosine wave.  Adjust amplitude and frequency as needed.  

        10000 * COS((m.Month * 2 * PI()) / 12) + 15000 AS ExpectedSales  -- Example: Amplitude=10000, Period = 12 months, Vertical Shift = 15000  

    FROM Months m  

    LEFT JOIN MonthlySales ms ON m.Month = ms.SaleMonth  

)  

SELECT  

    SaleMonth,  

    ActualSales,  

    ExpectedSales,  

    (ActualSales - ExpectedSales) AS SalesDifference  

FROM SalesAnalysis  

ORDER BY SaleMonth;  

            
        

Create SQL query with SqlQueryBuilder 4

            
var (sql4, parameters4) = new SqlQueryBuilder()  

    .WithCTETable(new Table("Months"), new SqlQueryBuilder().Select()  

        .Column(1, "Month")  

        .UnionAll(new SqlQueryBuilder().Select().Column(new ColumnArithmatic().Column("Month").ADD().Value(1), "Month"))  

        .From("Months")  

        .Where(new Where(new Column("Month").LessThan(12)))  

    )  

    .WithCTETable(new Table("MonthlySales"), new SqlQueryBuilder()  

        .Select()  

        .Column(new DATEPART(SqlDateInterval.month, new Column("o.OrderDate")), "SaleMonth")  

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

        .From("Orders", "o")  

        .Join(new List<IJoin>()  

        {  

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

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

        })  

        .Where(new Where(new YEAR(new Column("o.OrderDate")).Equale(1996)))  

        .GroupBy(new GroupBy(new DATEPART(SqlDateInterval.month, new Column("o.OrderDate"))))  

    )  

    .WithCTETable(new Table("SalesAnalysis"), new SqlQueryBuilder().Select()  

        .Column("m.Month", "SaleMonth")  

        .Column(new COALESCE(new Column("ms.TotalSales"), 0), "ActualSales")  

        .Column(new ColumnArithmatic(10000).MULTIPLY(new COS(new ColumnArithmatic().StartBracket("m.Month")  

            .MULTIPLY(2).MULTIPLY(new PI()).EndBracket().DIVIDE(12))).ADD(15000), "ExpectedSales")  

        .From("Months", "m")  

        .Join(new List<IJoin>()  

        {  

            new LEFTJOIN().TableName(new Table("MonthlySales","ms"))  

                .On(new Column("m.Month").Equale(new Column("ms.SaleMonth")))  

        })  

    )  

    .Select()  

    .Columns("SaleMonth", "ActualSales", "ExpectedSales")  

    .Column(new ColumnArithmatic("ActualSales").SUBTRACT("ExpectedSales"), "SalesDifference")  

    .From("SalesAnalysis")  

    .OrderBy(new OrderBy().SetColumnAscending("SaleMonth"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 4

            
WITH Months
AS (SELECT @pMAIN_2507200147313684940 AS Month
    UNION ALL
    SELECT Month + @pMAIN_2507200147313684941 AS Month
    FROM Months
    WHERE Month < @pMAIN_2507200147313684942),
 MonthlySales
AS (SELECT DATEPART(month, o.OrderDate) AS SaleMonth,
           SUM(od.Quantity * od.UnitPrice) AS TotalSales
    FROM Orders AS o
         INNER JOIN
         [Order Details] AS od
         ON o.OrderID = od.OrderID
    WHERE YEAR(o.OrderDate) = @pMAIN_2507200147313684943
    GROUP BY DATEPART(month, o.OrderDate)),
 SalesAnalysis
AS (SELECT m.Month AS SaleMonth,
           COALESCE (ms.TotalSales, @pMAIN_2507200147313684944) AS ActualSales,
           @pMAIN_2507200147313684945 * COS((m.Month * @pMAIN_2507200147313684946 * PI()) / @pMAIN_2507200147313684947) + @pMAIN_2507200147313684948 AS ExpectedSales
    FROM Months AS m
         LEFT OUTER JOIN
         MonthlySales AS ms
         ON m.Month = ms.SaleMonth)
SELECT SaleMonth,
       ActualSales,
       ExpectedSales,
       ActualSales - ExpectedSales AS SalesDifference
FROM SalesAnalysis
ORDER BY SaleMonth ASC;


            
        

Parameters (If used)

Query Results 4: