Window COUNT SQL function

1. Usage of COUNT to find customer distribution within country

SQL Server Query 1

            
WITH CustomerOrderCounts AS (  

    SELECT  

        c.CustomerID,  

        c.CompanyName,  

        c.Country,  

        COUNT(o.OrderID) AS TotalOrders  

    FROM  

        Customers c  

    LEFT JOIN  

        Orders o ON c.CustomerID = o.CustomerID  

    GROUP BY  

        c.CustomerID,  

        c.CompanyName,  

        c.Country  

),  

CountryOrderDistribution AS (  

    SELECT  

        Country,  

        TotalOrders,  

        CUME_DIST() OVER (PARTITION BY Country ORDER BY TotalOrders) + 1 AS CumulativeDistributionWithinCountry  

    FROM  

        CustomerOrderCounts  

)  

SELECT  

    coc.CustomerID,  

    coc.CompanyName,  

    coc.Country,  

    coc.TotalOrders,  

    cod.CumulativeDistributionWithinCountry  

FROM  

    CustomerOrderCounts coc  

JOIN  

    CountryOrderDistribution cod ON coc.Country = cod.Country AND coc.TotalOrders = cod.TotalOrders  

ORDER BY  

    coc.Country,  

    cod.CumulativeDistributionWithinCountry;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

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

        .Select()  

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

        .Column(new COUNT(new Column("o.OrderId")), "TotalOrders")  

        .From("Customers", "c")  

        .Join(new List<IJoin>()  

        {  

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

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

        })  

        .GroupBy(new GroupBy("c.CustomerID", "c.CompanyName", "c.Country"))  

    )  

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

        .Select()  

        .Columns("Country", "TotalOrders")  

        .Column(new CUME_DIST().PARTITION_BY(new Column("Country")).ORDER_BY(new OrderBy().Set(new Column("TotalOrders"))  

        ).AddArithmatic(new Arithmatic().Add(1)), "CumulativeDistributionWithinCountry")  

        .From("CustomerOrderCounts")  

    )  

    .Select()  

    .Columns("coc.CustomerID", "coc.CompanyName", "coc.Country", "coc.TotalOrders", "cod.CumulativeDistributionWithinCountry")  

    .From("CustomerOrderCounts", "coc")  

    .Join(new List<IJoin>()  

    {  

        new INNERJOIN().TableName(new Table("CountryOrderDistribution","cod"))  

            .On(new Column("coc.Country").Equale(new Column("cod.Country")))  

            .AND(new Column("coc.TotalOrders").Equale(new Column("cod.TotalOrders")))  

    })  

    .OrderBy(new OrderBy().Set(new Column("coc.Country")).Set(new Column("cod.CumulativeDistributionWithinCountry")))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH CustomerOrderCounts
AS (SELECT c.CustomerID,
           c.CompanyName,
           c.Country,
           COUNT(o.OrderId) AS TotalOrders
    FROM Customers AS c
         LEFT OUTER JOIN
         Orders AS o
         ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID, c.CompanyName, c.Country),
 CountryOrderDistribution
AS (SELECT Country,
           TotalOrders,
           CUME_DIST() OVER (PARTITION BY Country ORDER BY TotalOrders ASC) + @pMAIN_2507192107352674730 AS CumulativeDistributionWithinCountry
    FROM CustomerOrderCounts)
SELECT coc.CustomerID,
       coc.CompanyName,
       coc.Country,
       coc.TotalOrders,
       cod.CumulativeDistributionWithinCountry
FROM CustomerOrderCounts AS coc
     INNER JOIN
     CountryOrderDistribution AS cod
     ON coc.Country = cod.Country
        AND coc.TotalOrders = cod.TotalOrders
ORDER BY coc.Country ASC, cod.CumulativeDistributionWithinCountry ASC;


            
        

Parameters (If used)

Query Results 1: