COUNT_BIG SQL function

1. Usage of COUNT_BIG to show customer demographics

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

            
SELECT c.CustomerID,
       c.CompanyName,
       c.ContactName,
       c.Country,
       COUNT(o.OrderID) AS TotalOrders,
       AVG(CAST (od.Quantity * od.UnitPrice * (@pMAIN_2507200158528374730 - od.Discount) AS DECIMAL (18, 2))) AS AverageOrderValue,
       MAX(CAST (od.Quantity * od.UnitPrice * (@pMAIN_2507200158528374731 - od.Discount) AS DECIMAL (18, 2))) AS MaxOrderValue,
       MIN(CAST (od.Quantity * od.UnitPrice * (@pMAIN_2507200158528374732 - od.Discount) AS DECIMAL (18, 2))) AS MinOrderValue
FROM Customers AS c
     INNER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
     INNER JOIN
     [Order Details] AS od
     ON o.OrderID = od.OrderID
GROUP BY c.CustomerID, c.CompanyName, c.ContactName, c.Country
ORDER BY TotalOrders DESC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of COUNT_BIG to find most valuable customers based on orders

SQL Server Query 2

            
--    Identify customers with a very large number of orders (e.g., more than 100)  

--    This helps in identifying your most valuable customers.  

SELECT  

    c.CustomerID,  

    c.CompanyName,  

    COUNT_BIG(o.OrderID) AS TotalOrders  

FROM Customers AS c  

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

GROUP BY c.CustomerID, c.CompanyName  

HAVING COUNT_BIG(o.OrderID) > 10  -- Use COUNT_BIG in the HAVING clause  

ORDER BY TotalOrders DESC;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

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

    .Column(new COUNT_BIG(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.CustomerID", "CompanyName"))  

    .Having(new Having(new COUNT_BIG(new Column("o.OrderID")).GreaterThan(10)))  

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

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT c.CustomerID,
       c.CompanyName,
       COUNT(o.OrderID) AS TotalOrders
FROM Customers AS c
     INNER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, CompanyName
HAVING COUNT(o.OrderID) > @pMAIN_2507200158528598090
ORDER BY TotalOrders DESC;


            
        

Parameters (If used)

Query Results 2:

3. Usage of CHECKSUM to find distribution of orders by ship country

SQL Server Query 3

            
-- Analyze the distribution of orders by ship country, using COUNT_BIG  

SELECT  

    o.ShipCountry,  

    COUNT_BIG(o.OrderID) AS OrderCount,  

    CAST(COUNT_BIG(o.OrderID) * 100.0 / (SELECT COUNT_BIG(*) FROM Orders) AS DECIMAL(5, 2)) AS PercentageOfTotalOrders  

FROM Orders AS o  

GROUP BY o.ShipCountry  

ORDER BY OrderCount DESC;  

  

  

            
        

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .Select()  

    .Column("o.ShipCountry", "ShipCountry")  

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

    .Column(new CAST(new ColumnArithmatic(new COUNT_BIG(new Column("o.OrderID"))).MULTIPLY(100.0)  

        .DIVIDE(new SqlQueryBuilder()  

                .Select()  

                .Column(new COUNT_BIG(new Column("*")),"OrderCount")  

                .From("Orders")), SqlDataType.DECIMAL, new Tuple<int,int>(5, 2)), "PercentageOfTotalOrders")  

    .From("Orders", "o")  

    .GroupBy(new GroupBy("o.ShipCountry"))  

    .OrderBy(new OrderBy().SetColumnDescending("OrderCount"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT o.ShipCountry AS ShipCountry,
       COUNT(o.OrderID) AS OrderCount,
       CAST (COUNT(o.OrderID) * @pMAIN_2507200158528691430 / (SELECT COUNT(*) AS OrderCount
                                                              FROM Orders) AS DECIMAL (5, 2)) AS PercentageOfTotalOrders
FROM Orders AS o
GROUP BY o.ShipCountry
ORDER BY OrderCount DESC;


            
        

Parameters (If used)

Query Results 3:

4. Usage of COUNT_BIG as windows function to show change in customer data

SQL Server Query 4

            
-- Find orders per customer  

SELECT  

    o.OrderID,  

    o.CustomerID,  

    COUNT_BIG(*) OVER (PARTITION BY o.CustomerID) AS OrdersPerCustomer  

FROM  

    Orders o;  

            
        

Create SQL query with SqlQueryBuilder 4

            
var (sql4, parameters4) = new SqlQueryBuilder()  

    .Select()  

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

    .Column(new COUNT_BIG(new Column("*")).OVER(new OVER().PARTITION_BY(new Column("o.CustomerID"))), "OrdersPerCustomer")  

    .From("Orders", "o")  

    .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT o.OrderID,
       o.CustomerID,
       COUNT(*) OVER (PARTITION BY o.CustomerID) AS OrdersPerCustomer
FROM Orders AS o;


            
        

Parameters (If used)

Query Results 4:

4. Usage of COUNT_BIG with DISTINCT to find distinct supplier

SQL Server Query 5

            
-- Find distinct count of suppliers  

SELECT  

    COUNT_BIG(DISTINCT SupplierID) AS NumberOfSuppliers  

FROM  

    Suppliers;  

            
        

Create SQL query with SqlQueryBuilder 5

            
var (sql5, parameters5) = new SqlQueryBuilder()  

    .Select()  

    .Column(new COUNT_BIG(new Column("SupplierID"), true), "NumberOfSuppliers")  

    .From("Suppliers")  

    .Build();  

            
        

Query build by SqlQueryBuilder 5

            
SELECT COUNT(DISTINCT SupplierID) AS NumberOfSuppliers
FROM Suppliers;


            
        

Parameters (If used)

Query Results 5: