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_2512060920293783740 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:

2. Usage of COUNT with DISTINCT find customer suppliers

SQL Server Query 2

            
WITH CustomerProductCounts AS (  

    SELECT  

        c.CustomerID,  

        c.CompanyName AS CustomerName,  

        COUNT(DISTINCT od.ProductID) AS CustomerProductCount,  

        CAST(COUNT(DISTINCT od.ProductID) AS DECIMAL(10, 8)) / (SELECT MAX(sub.ProductCount) from (SELECT  COUNT(DISTINCT od.ProductID) as ProductCount from Customers c join Orders o on c.CustomerID = o.CustomerID join [Order Details] od on o.OrderID = od.OrderID GROUP BY c.CustomerID) as sub) AS NormalizedCustomerProductCount  

    FROM Customers c  

    JOIN Orders o ON c.CustomerID = o.CustomerID  

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

    GROUP BY c.CustomerID, c.CompanyName  

),  

SupplierProductCounts AS (  

    SELECT  

        s.SupplierID,  

        s.CompanyName AS SupplierName,  

        COUNT(DISTINCT p.ProductID) AS SupplierProductCount,  

            CAST(COUNT(DISTINCT p.ProductID) AS DECIMAL(10, 8)) / (SELECT MAX(SupplierProductCount) FROM (SELECT s.SupplierID, COUNT(DISTINCT p.ProductID) AS SupplierProductCount from Suppliers s JOIN Products p ON s.SupplierID = p.SupplierID GROUP BY s.SupplierID) as sub2) AS NormalizedSupplierProductCount  

    FROM Suppliers s  

    JOIN Products p ON s.SupplierID = p.SupplierID  

    GROUP BY s.SupplierID, s.CompanyName  

),  

CustomerSupplierPairs AS (  

    SELECT  

        cp.CustomerID,  

        cp.CustomerName,  

        sp.SupplierID,  

        sp.SupplierName,  

        -- Calculate a similarity value  

        (cp.NormalizedCustomerProductCount * sp.NormalizedSupplierProductCount) AS SimilarityValue  

    FROM CustomerProductCounts cp  

    CROSS JOIN SupplierProductCounts sp  

    WHERE cp.NormalizedCustomerProductCount > 0 AND sp.NormalizedSupplierProductCount > 0  

),  

AcosResult as (  

    SELECT  

        CustomerID,  

        CustomerName,  

        SupplierID,  

        SupplierName,  

        SimilarityValue,  

        CASE  

            WHEN SimilarityValue > 1 THEN 1  

            WHEN SimilarityValue < -1 THEN -1  

            ELSE SimilarityValue  

        END AS AdjustedSimilarityValue,  

        ACOS(  

        CASE  

            WHEN SimilarityValue > 1 THEN 1  

            WHEN SimilarityValue < -1 THEN -1  

            ELSE SimilarityValue  

        END  

        ) AS RelationshipAngleInRadians  

    FROM CustomerSupplierPairs  

)  

SELECT TOP 10  

    CustomerID,  

    CustomerName,  

    SupplierID,  

    SupplierName,  

    RelationshipAngleInRadians,  

    DEGREES(RelationshipAngleInRadians) AS RelationshipAngleInDegrees  

FROM AcosResult  

ORDER BY RelationshipAngleInRadians;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

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

        .Select()  

        .Column("c.CustomerID", "CustomerID")  

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

        .Column(new COUNT(new Column("od.ProductID"), true), "CustomerProductCount")  

        .Column(new ColumnArithmatic()  

            .SqlFunction(new CAST(new COUNT(new Column("od.ProductID"), true), SqlDataType.DECIMAL, new Tuple<int, int>(10, 8)))  

            .DIVIDE()  

            .Column(new SqlQueryBuilder()  

                .Select()  

                .Column(new MAX(new Column("sub.ProductCount")), "NormalizedCustomerProductCount")  

                .From(new SqlQueryBuilder()  

                    .Select()  

                    .Column(new COUNT(new Column("od.ProductID"), true), "ProductCount")  

                    .From("Customers", "c")  

                    .Join(new List<IJoin>()  

                    {  

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

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

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

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

                    })  

                    .GroupBy(new GroupBy(new Column("c.CustomerID")))  

                , "sub")  

            )  

        , "NormalizedCustomerProductCount")  

        .From("Customers", "c")  

        .Join(new List<IJoin>()  

        {  

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

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

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

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

        })  

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

    )  

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

        .Select()  

        .Column("s.SupplierID", "SupplierID")  

        .Column("s.CompanyName", "SupplierName")  

        .Column(new COUNT(new Column("p.ProductID"), true), "SupplierProductCount")  

        .Column(new ColumnArithmatic()  

            .SqlFunction(new CAST(new COUNT(new Column("p.ProductID"), true), SqlDataType.DECIMAL, new Tuple<int, int>(10, 8)))  

            .DIVIDE()  

            .Column(new SqlQueryBuilder().Select()  

                .Column(new MAX(new Column("SupplierProductCount")), "NormalizedSupplierProductCount")  

                .From(new SqlQueryBuilder()  

                    .Select()  

                    .Column("s.SupplierID", "SupplierID")  

                    .Column(new COUNT(new Column("p.ProductID"), true), "SupplierProductCount")  

                    .From("Suppliers", "s")  

                    .Join(new List<IJoin>()  

                    {  

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

                            .On(new Column("s.SupplierID").Equale(new Column("p.SupplierID")))  

                    })  

                    .GroupBy(new GroupBy(new Column("s.SupplierID")))  

                , "sub2")  

            )  

        , "NormalizedSupplierProductCount")  

        .From("Suppliers", "s")  

        .Join(new List<IJoin>()  

        {  

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

                .On(new Column("s.SupplierID").Equale(new Column("p.SupplierID")))  

        })  

        .GroupBy(new GroupBy(new Column("s.SupplierID"), new Column("s.CompanyName")))  

    )  

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

        .Select()  

        .Columns("cp.CustomerID", "cp.CustomerName", "sp.SupplierID", "sp.SupplierName")  

        .Column(new ColumnArithmatic().StartBracket().Column("cp.NormalizedCustomerProductCount").MULTIPLY()  

            .Column("sp.NormalizedSupplierProductCount").EndBracket()  

        , "SimilarityValue")  

        .From("CustomerProductCounts", "cp")  

        .Join(new List<IJoin>()  

        {  

            new CROSSJOIN().TableName(new Table("SupplierProductCounts","sp"))  

        })  

        .Where(new Where(new Column("cp.NormalizedCustomerProductCount").Equale(0))  

            .AND(new Column("sp.NormalizedSupplierProductCount").Equale(0)))  

    )  

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

        .Select()  

        .Columns("CustomerID", "CustomerName", "SupplierID", "SupplierName", "SimilarityValue")  

        .Column(new CASE()  

            .When(new Column("SimilarityValue").GreaterThan(1))  

            .Then(1)  

            .When(new Column("SimilarityValue").GreaterThan(-1))  

            .Then(-1)  

            .Else(new Column("SimilarityValue"))  

        , "AdjustedSimilarityValue")  

        .Column(new ACOS(new CASE()  

            .When(new Column("SimilarityValue").GreaterThan(1))  

            .Then(1)  

            .When(new Column("SimilarityValue").GreaterThan(-1))  

            .Then(-1)  

            .Else(new Column("SimilarityValue")))  

        , "RelationshipAngleInRadians")  

        .From("CustomerSupplierPairs")  

    )  

    .Select().Top(10)  

    .Columns("CustomerID", "CustomerName", "SupplierID", "SupplierName", "RelationshipAngleInRadians")  

    .Column(new DEGREES(new Column("RelationshipAngleInRadians")), "RelationshipAngleInDegrees")  

    .From("AcosResult")  

    .OrderBy(new OrderBy().SetColumnAscending("RelationshipAngleInRadians"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
WITH CustomerProductCounts
AS (SELECT c.CustomerID AS CustomerID,
           c.CompanyName AS CustomerName,
           COUNT(DISTINCT od.ProductID) AS CustomerProductCount,
           CAST (COUNT(DISTINCT od.ProductID) AS DECIMAL (10, 8)) / (SELECT MAX(sub.ProductCount) AS NormalizedCustomerProductCount
                                                                     FROM (SELECT COUNT(DISTINCT od.ProductID) AS ProductCount
                                                                           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) AS sub) AS NormalizedCustomerProductCount
    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),
 SupplierProductCounts
AS (SELECT s.SupplierID AS SupplierID,
           s.CompanyName AS SupplierName,
           COUNT(DISTINCT p.ProductID) AS SupplierProductCount,
           CAST (COUNT(DISTINCT p.ProductID) AS DECIMAL (10, 8)) / (SELECT MAX(SupplierProductCount) AS NormalizedSupplierProductCount
                                                                    FROM (SELECT s.SupplierID AS SupplierID,
                                                                                 COUNT(DISTINCT p.ProductID) AS SupplierProductCount
                                                                          FROM Suppliers AS s
                                                                               INNER JOIN
                                                                               Products AS p
                                                                               ON s.SupplierID = p.SupplierID
                                                                          GROUP BY s.SupplierID) AS sub2) AS NormalizedSupplierProductCount
    FROM Suppliers AS s
         INNER JOIN
         Products AS p
         ON s.SupplierID = p.SupplierID
    GROUP BY s.SupplierID, s.CompanyName),
 CustomerSupplierPairs
AS (SELECT cp.CustomerID,
           cp.CustomerName,
           sp.SupplierID,
           sp.SupplierName,
           (cp.NormalizedCustomerProductCount * sp.NormalizedSupplierProductCount) AS SimilarityValue
    FROM CustomerProductCounts AS cp CROSS JOIN SupplierProductCounts AS sp
    WHERE cp.NormalizedCustomerProductCount > @pMAIN_2512060920293933940
          AND sp.NormalizedSupplierProductCount > @pMAIN_2512060920293933941),
 AcosResult
AS (SELECT CustomerID,
           CustomerName,
           SupplierID,
           SupplierName,
           SimilarityValue,
           CASE WHEN SimilarityValue > @pMAIN_2512060920293933942 THEN @pMAIN_2512060920293933943 WHEN SimilarityValue > @pMAIN_2512060920293933944 THEN @pMAIN_2512060920293933945 ELSE SimilarityValue END AS AdjustedSimilarityValue,
           ACOS(CASE WHEN SimilarityValue > @pMAIN_2512060920293933946 THEN @pMAIN_2512060920293933947 WHEN SimilarityValue > @pMAIN_2512060920293933948 THEN @pMAIN_2512060920293933949 ELSE SimilarityValue END) AS RelationshipAngleInRadians
    FROM CustomerSupplierPairs)
SELECT TOP 10 CustomerID,
              CustomerName,
              SupplierID,
              SupplierName,
              RelationshipAngleInRadians,
              DEGREES(RelationshipAngleInRadians) AS RelationshipAngleInDegrees
FROM AcosResult
ORDER BY RelationshipAngleInRadians ASC;


            
        

Parameters (If used)

Query Results 2: