2

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.

1 Answer 1

3

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

2
  • Didn't realize that I needed to group by the subGrp in the subquery. Doing the group by in the main makes sense. Also didn't know how to reference the subquery in the main. Now, if there was another column I needed to add that needs filtering in the vendor table, would I put that filter in the subquery as well (ex: looking for 1 value of 4)? The way I understand it, it looks like I could add that to the subquery, then reference it in the main?
    – tkmagnet
    Feb 22 at 15:04
  • It would go in the inside of the subquery. Depends if you want to filter the total count, then put it in the WHERE, if you only want to filter prodType then put it inside the CASE in the conditional aggregation. Feb 22 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.