Blake Rivell Blake Rivell - 2 months ago 12
SQL Question

Querying a column that shows [count] of [max count]

I have 3 tables:

CustomerTypes table

Customers table (has a foreign key CustomerType). A customer can only have one customer type.

CustomersCollection table (contains many customerIds)

The primary SELECT of the query will be on CustomerTypes. I will be selecting two columns: CustomerTypeName and CountInCollection

The CustomerCount column in my query needs to show something like the following:

[Total # of Customers in CustomerType that are in Customer Collection] Of [Total # of Customers in CustomerType]

How can I get the proper customer count of the CustomerType that is part of the collection?

Example:


  • Customer1, Customer2, and Customer3 are all of CustomerTypeA.

  • CustomerCollection1 has customers Customer1 and Customer2 in it. The CountInCollection column for the CustomerTypeA record should show '2 of 3'.



enter image description here

Here is how I am able to get each count in separate queries:

-- Total customers in customer collection of customer type
SELECT COUNT(c.Id)
FROM Customer c
INNER JOIN CustomerCollection cc ON c.Id = cc.CustomerId
WHERE cc.CollectionId = 1019 AND c.CustomerTypeId=1000

-- Total customers in customer type
SELECT COUNT(Id) FROM
Customer WHERE CustomerTypeId=1000

Answer

Since you are using SQL 2008, I would take advantage of Common Table Expressions, aka CTEs, to assemble the data.

First, we'll need some test data. NOTE: I've thrown in some 'outliers' so that you can see where this kind of logic can bite you later.

DECLARE @CustomerTypes TABLE
    (
    CustomerTypeID INT,
    [Customer Type] VARCHAR(100)
    )

INSERT INTO @CustomerTypes
    SELECT 1, 'TypeA'
    UNION SELECT 2, 'TypeB'
    UNION SELECT 3, 'TypeC'  --NOTE: An outlier (not in customers-collection)
    UNION SELECT 4, 'TypeD'  --NOTE: An outlier (not in customers)

DECLARE @Customers TABLE
    (
    CustomerID INT,
    CustomerTypeID INT
    )

INSERT INTO @Customers
    SELECT 1, 1
    UNION SELECT 2, 1
    UNION SELECT 3, 1
    UNION SELECT 4, 2
    UNION SELECT 5, 2
    UNION SELECT 6, 2
    UNION SELECT 7, 3  

DECLARE @CustomersCollection TABLE
    (
    CollectionID INT IDENTITY(1,1),
    CustomerID INT
    )

INSERT INTO @CustomersCollection
    (CustomerID)
    SELECT TOP 2  --INSERT 2 of 3
        CustomerID FROM @Customers WHERE CustomerTypeID = 1  --TypeA

INSERT INTO @CustomersCollection
    (CustomerID)
    SELECT TOP 1  --INSERT 1 of 3
        CustomerID FROM @Customers WHERE CustomerTypeID = 2  --TypeB

Second, assemble the CTE data, and generate your output

;WITH CTE_COUNT_TYPE(CustomerTypeID, TypeCount)
AS
(
    SELECT CustomerTypeID, COUNT(1)
    FROM @Customers
    GROUP BY CustomerTypeID
)
--SELECT * FROM CTE_COUNT_TYPE  --DEBUG
,
CTE_COUNT_COLLECTION(CustomerTypeID, CollectionCount)
AS
(
    SELECT CustomerTypeID, COUNT(1)
    FROM @CustomersCollection CC
        INNER JOIN @Customers C 
            ON CC.CustomerID = C.CustomerID
    GROUP BY CustomerTypeID
)
--SELECT * FROM CTE_COUNT_COLLECTION  --DEBUG
SELECT [Customer Type],
    --CONVERT is necessary to combine INT data type (i.e. Count) and VARCHAR data type (i.e. 'as')
    CONVERT(VARCHAR(100), COALESCE(CCC.CollectionCount, 0)) + 
        ' of ' + 
        CONVERT(VARCHAR(100), COALESCE(CCT.TypeCount, 0)) As [Count in Collection]

    FROM @CustomerTypes CT
    LEFT OUTER JOIN @Customers C  --Left outer join assists in outliers
        ON CT.CustomerTypeID = C.CustomerTypeID
    LEFT OUTER JOIN CTE_COUNT_TYPE CCT  --Left outer join assists in outliers
        ON CCT.CustomerTypeID = CT.CustomerTypeID
    LEFT OUTER JOIN CTE_COUNT_COLLECTION CCC  --Left outer join assists in outliers
        ON CCC.CustomerTypeID = CT.CustomerTypeID
    GROUP BY CT.[Customer Type]
        , CCC.CollectionCount
        , CCT.TypeCount