How to builld a table of aggregated DISTINCT Counts by ID - SQL

I'm attempting to build a "product penetration table by Reseller"

I have three tables, Resellers, Products1, and Products2.

Table 1:

ResellerID
001
002
003

Table 2--------------------------------------------Table 3

Customer#  -  Prod#  - ResellerID  |  Customer#  -  Prod#  - ResellerID
ABC        -  1-2    - 001         |     GHI        -  9-8    - 001
DEF        -  3-4    - 002         |     JKL        -  9-8    - 002
ABC        -  3-4    - 001         |     MNO        -  7-6    - 003

I need the end table result to look like this:

ResellerID(PK)  -  (Unique)CountofCustomer  - (Unique)CountofProduct
001             -        2                  -      3
002             -        2                  -      2
003             -        1                  -      1

I built the basic table to be a reference as 1 customer table, I built the Final table, but I can't figure out how to take the unique counts BY Reseller and insert them back into the appropriate row (After which the math will be applied to give me the penetration).

Any help on this would be mucho appreciated.

Thanks

1 answer

  • answered 2018-10-11 19:53 Gordon Linoff

    I would suggest union all and group by:

    select ResellerID, count(distinct Customer), count(distinct prod)
    from ((select Customer, Prod, ResellerID from table2
          ) union all
          (select Customer, Prod, ResellerID from table3
          )
         ) t
    group by ResellerID;