Query for single return meeting multiple criteria and values

Question

I'm trying to figure out a way to count an ID once when multiple criteria are met in one column while displaying a total of the same criteria in another column. The criteria for the first column can be used more than once for the same ID, but need to be counted only once. There are 2 different subGrp values (1 and 2), but will only focus on one. Here is the sample data for the 2 tables I'm using:

create table vendor (
    subGrp char(1),
    subID varchar(10),
    subStatus char(1),
    subCat char(1) );

insert into vendor (
    subGrp, subID, subStatus, subCat)
values ('1','101','A','E'),
    ('1','105','A','F'),
    ('1','127','A','F'),
    ('1','132','A','F'),
    ('1','133','D','F'),
    ('1','154','A','F'),
    ('1','162','A','F'),
    ('1','167','W','E'),
    ('1','178','A','F'),
    ('1','196','A','F'),
    ('1','200','A','E'),
    ('1','205','A','F'),
    ('1','230','A','F'),
    ('1','231','A','F'),
    ('1','237','A','F'),
    ('1','259','A','F'),
    ('1','260','A','F'),
    ('1','271','A','F');

create table customer (
    cusID varchar(10),
    prodType char(1) );

insert into customer (
    cusID, prodType
values ('101','Y'),
    ('105','H'),
    ('105','Y'),
    ('127','H'),
    ('132','H'),
    ('132','Y'),
    ('132','Y'),
    ('132','F'),
    ('132','Y'),
    ('133','H'),
    ('133','S'),
    ('154','H'),
    ('154','Y'),
    ('154','Y'),
    ('154','Y'),
    ('154','Y'),
    ('162','H'),
    ('162','Y'),
    ('162','Y'),
    ('167','J'),
    ('178','H'),
    ('196','H'),
    ('200','Y'),
    ('205','H'),
    ('205','Y'),
    ('230','H'),
    ('230','Y'),
    ('230','Y'),
    ('230','Y'),
    ('230','Y'),
    ('231','H'),
    ('237','H'),
    ('259','H'),
    ('259','Y'),
    ('260','H'),
    ('271','H');

The subGrp will be in the first column. In the next column, I need to count how many cusID's have a Y, F, or J prodType, while having a subStatus of A and subCat of F. The only values that are used multiple times are found in prodType.

I want the following results:

subGrp	prodTypeTotal	subGrpTotal
1	7	14

So, in subGrp 1, there are 7 ID's counted only once if ID contains any combination of Y, F, or J, and having a total of 14 ID's that have an A subStatus and F subCat.

It is a third party application and database so I'm a little limited in what I can do (ex: throws error when using distinct: scalar function not found). They have told me that it is MS SQL.

Also, I'm not sure about the datatypes as I am not privy to that but this is what I'm assuming. Hope this makes sense. Thanks in advance.

Answer 1

Start by joining the tables together, with the right filters on vendors in place.

Looks like you need to group by subGrp and cusID to check for those Y, F, J values, then group again by subGrp to get the totals.

You can use a conditional MIN to just get 1 or null for every cusID which has any of those three values.

SELECT
  v.subGrp,
  SUM(v.prodType) AS prodTypeTotal,
  COUNT(*) AS subGrpTotal
FROM (
    SELECT
      v.subGrp,
      c.cusID,
      MIN(CASE WHEN c.prodType IN ('Y', 'F', 'J') THEN 1 END) AS prodType
    FROM vendor v
    JOIN customer c ON c.cusID = v.subID
    WHERE v.subStatus = 'A'
      AND v.subCat = 'F'
    GROUP BY
      v.subGrp,
      c.cusID
) v
GROUP BY
  v.subGrp;

db<>fiddle