SQL Count number of occurences of combinations and split into separate columns by count-CodePudding

I have a SQL table with vendors and companies:

Vendor	Company
V1	C1
V2	C1
V3	C1
V4	C1
V1	C2
V2	C2
V4	C2
V1	C3
V2	C3
V5	C3
V6	C3

I am trying to show how many unique and shared vendors each company has:

Company	UniqueVendors	SharedVendors
C1	1	3
C2	1	2
C3	2	2

I am able to identify Unique / Shared vendors by counting there occurences, e.g.


    SELECT [Vendor], COUNT([Vendor]) AS VendorCount 
    FROM table
    GROUP BY Vendor
    HAVING COUNT([Vendor]) = 1

but I do not know how to proceed and count the shared vendors and then get the result in separate columns.

CodePudding user response：

We can use conditional aggregation along with exists logic:

WITH cte AS (
    SELECT Company,
           CASE WHEN NOT EXISTS (
               SELECT 1
               FROM yourTable t2
               WHERE t2.Company <> t1.Company AND
                     t2.Vendor = t1.Vendor
           )
                THEN 1 ELSE 0 END AS unique_cnt
    FROM yourTable t1
)

SELECT Company,
       SUM(unique_cnt) AS UniqueVendors,
       COUNT(*) - SUM(unique_cnt) AS SharedVendors
FROM cte
GROUP BY Company;