SELECT predicate WHERE and GROUP BY

1. Total Orders per Customer

SQL Server Query 1

            
 SELECT TOP 30  

     C.CompanyName,  

     COUNT(O.OrderID) AS TotalOrders  

 FROM  

     Customers AS C  

 JOIN  

     Orders AS O ON C.CustomerID = O.CustomerID  

 GROUP BY  

     C.CompanyName  

 ORDER BY  

     TotalOrders DESC;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select().Top(30)  

     .Column("c.CompanyName")  

     .Column(new COUNT(new Column("O.OrderID")), "TotalOrders")  

     .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.CompanyName"))  

     .OrderBy(new OrderBy().SetColumnDescending("TotalOrders"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 30 c.CompanyName,
              COUNT(O.OrderID) AS TotalOrders
FROM Customers AS C
     INNER JOIN
     Orders AS O
     ON C.CustomerID = O.CustomerID
GROUP BY C.CompanyName
ORDER BY TotalOrders DESC;


            
        

Parameters (If used)

Query Results 1:

2. Total Sales Value per Employee per Year

SQL Server Query 2

            
 SELECT  

     E.FirstName + ' ' + E.LastName AS EmployeeName,  

     YEAR(O.OrderDate) AS OrderYear,  

     SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS TotalSalesValue  

 FROM  

     Employees AS E  

 JOIN  

     Orders AS O ON E.EmployeeID = O.EmployeeID  

 JOIN  

     [Order Details] AS OD ON O.OrderID = OD.OrderID  

 GROUP BY  

     E.FirstName,  

     E.LastName,  

     YEAR(O.OrderDate)  

 ORDER BY  

     EmployeeName,  

     OrderYear;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Column(new CONCAT(new Column("E.FirstName"), " ", new Column("E.LastName")), "EmployeeName")  

     .Column(new YEAR(new Column("O.OrderDate")), "OrderYear")  

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

         .MULTIPLY().StartBracket(1).SUBTRACT(new Column("OD.Discount")).EndBracket()), "TotalSalesValue")  

     .From("Employees", "E")  

     .Join(new List<IJoin>()  

     {  

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

             .On(new Column("E.EmployeeID").Equale(new Column("O.EmployeeID"))),  

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

             .On(new Column("O.OrderID").Equale(new Column("OD.OrderID")))  

     })  

     .GroupBy(new GroupBy(new Column("E.FirstName"), new Column("E.LastName"),  

         new YEAR(new Column("O.OrderDate"))))  

     .OrderBy(new OrderBy().SetColumnAscending("EmployeeName")  

         .SetColumnAscending("OrderYear"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT CONCAT(E.FirstName, @pMAIN_2507200205126502650, E.LastName) AS EmployeeName,
       YEAR(O.OrderDate) AS OrderYear,
       SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200205126502651 - OD.Discount)) AS TotalSalesValue
FROM Employees AS E
     INNER JOIN
     Orders AS O
     ON E.EmployeeID = O.EmployeeID
     INNER JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
GROUP BY E.FirstName, E.LastName, YEAR(O.OrderDate)
ORDER BY EmployeeName ASC, OrderYear ASC;


            
        

Parameters (If used)

Query Results 2:

3. Calculate the total sales value for each month of every year.

SQL Server Query 3

            
 SELECT  

     YEAR(O.OrderDate) AS OrderYear,  

     MONTH(O.OrderDate) AS OrderMonth,  

     SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS TotalSalesValue  

 FROM  

     Orders AS O  

 JOIN  

     [Order Details] AS OD ON O.OrderID = OD.OrderID  

 GROUP BY  

     YEAR(O.OrderDate),  -- Function used directly in GROUP BY  

     MONTH(O.OrderDate)  -- Function used directly in GROUP BY  

 ORDER BY  

     OrderYear,  

     OrderMonth;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Column(new YEAR(new Column("O.OrderDate")), "OrderYear")  

     .Column(new MONTH(new Column("O.OrderDate")), "OrderMonth")  

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

         .MULTIPLY().StartBracket(1).SUBTRACT("OD.Discount").EndBracket()), "TotalSalesValue")  

     .From("Orders", "O")  

     .Join(new List<IJoin>()  

     {  

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

             .On(new Column("O.OrderID").Equale(new Column("OD.OrderID")))  

     })  

     .GroupBy(new GroupBy(new YEAR(new Column("O.OrderDate")), new MONTH(new Column("O.OrderDate"))))  

     .OrderBy(new OrderBy()  

         .SetColumnAscending("OrderYear")  

         .SetColumnAscending("OrderMonth"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT YEAR(O.OrderDate) AS OrderYear,
       MONTH(O.OrderDate) AS OrderMonth,
       SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200205126655620 - OD.Discount)) AS TotalSalesValue
FROM Orders AS O
     INNER JOIN
     [Order Details] AS OD
     ON O.OrderID = OD.OrderID
GROUP BY YEAR(O.OrderDate), MONTH(O.OrderDate)
ORDER BY OrderYear ASC, OrderMonth ASC;


            
        

Parameters (If used)

Query Results 3: