SQL - GROUP BY & HAVING COUNT issue

Question

I have researched and found questions/answers like my issue here but nothing I can find exactly fits.

SELECTING with multiple WHERE conditions on same column --- This link is VERY close but still doesn't work for my issue.

Problem: I have a table that houses Payment Methods and Payment Status (blank status is OK)

SEL_PRO_PMTMETHOD_PK    SEL_PROFILE_DETAIL_FK   PMT_TYPE    PMT_STATUS
43827                   342997                  EFT         G
43828                   342997                  EFT         P
43829                   342997                  RCC  
43826                   342997                  EFT  

43776                   342922                  EFT  
43777                   342922                  EFT         G
43778                   342922                  EFT         P

I need to develop unique output by SEL_PROFILE_DETAIL_FK depending on whether it has ONLY EFTs entries, ONLY RCC entries, or BOTH EFT and RCC entries.

I figured out the easy stuff for ONLY EFT and ONLY RCC

But I need to be able to tell if there is BOTH EFT and RCC entries in the FK grouping.

My code so far:

SELECT pmt_type
FROM   sel_pro_pmtmethod
WHERE  sel_profile_detail_fk = '342997'    
  AND  pmt_type IN ('EFT', 'RCC')
GROUP  BY pmt_type
HAVING COUNT(distinct pmt_type) >= 1

This code is returning back pmt_type for both sets of data above. It doesn't matter if it has BOTH EFT and RCC, or just the EFT.

I have changed the HAVING COUNT clause to be "= 2" but that returns nothing because the count of RCCs = 1 and the EFTs = 3.

But what I need is for this WHERE / GROUP BY / HAVING scenario to be true is if there is an EFT AND RCC entry in the grouping. If one is missing then it fails.

`HAVING COUNT(distinct pmt_type) = 2` to should yield your desire results as `DISTINCT` will only count 1 occurrence of each value so it will only count `EFT` once and `RCC` once. Oh and `GROUP BY SEL_PROFILE_DETAIL_FK` not `pmt_type` — Matt, Dec 09 '16 at 17:14
Looks like you were closure when used HAVING COUNT = 2, that part you can change to a simple query but only 2 columns SEL_PROFILE_DETAIL_FK and PMT_TYPE. Group by only SEL_PROFILE_DETAIL_FK but in where clause put pmt_type IN ('EFT', 'RCC'). No Having is required. Use it as a subquery and attach/join to any query with its SEL_PROFILE_DETAIL_FK — , Dec 09 '16 at 17:15
Are EFT and RCC the **only** payment types? If there may be more than two payment types, and you are only interested in these two, you can modify both answers provided so far (which are both correct if there can be only two payment types) - the HAVING clause can be changed to something like `having count(case when pmt_type = 'EFT' then 'x' end) > 0 and count(case when pmt_type = 'RCC' then 'x' end) > 0`. — , Dec 09 '16 at 17:53

score 1 · Answer 1 · answered Dec 09 '16 at 17:08

1

Try this:

SELECT SEL_PROFILE_DETAIL_FK
FROM   sel_pro_pmtmethod
WHERE  pmt_type IN ('EFT', 'RCC')
GROUP  BY SEL_PROFILE_DETAIL_FK
HAVING MIN(pmt_type) <> MAX(pmt_type)

answered Dec 09 '16 at 17:08

Anand

1,165
10
18

+1 this method works too because it is grouped correctly and compares that the MAX & MIN are not the same type – Matt Dec 09 '16 at 17:18

Matt · Accepted Answer · 2016-12-09T17:29:49.773

SELECT SEL_PROFILE_DETAIL_FK
FROM   sel_pro_pmtmethod
WHERE  pmt_type IN ('EFT', 'RCC')
GROUP  BY SEL_PROFILE_DETAIL_FK
HAVING COUNT(distinct pmt_type) = 2

Your group by was wrong. grouping by pmt_type will mean it will show only 1 type per row. Because you want it by the foreign key you need to group by that.

DISTINCT will mean that it will only count 1 occurrence of each value.

And if you actually want all of the records related you can use window functions and conditional aggregation:

SELECT *
FROM
    (
       SELECT
          *
          ,COUNT(CASE WHEN PMT_TYPE = 'EFT' THEN PMT_TYPE END) OVER (PARTITION BY SEL_PROFILE_DETAIL_FK) EftCount
          ,COUNT(CASE WHEN PMT_TYPE = 'RCC' THEN PMT_TYPE END) OVER (PARTITION BY SEL_PROFILE_DETAIL_FK) RCCCount
       FROM
          sel_pro_pmtmethod
       WHERE
          PMT_TYPE IN ('EFT','RCC')
    ) t
WHERE
    t.EftCount > 0
    AND t.RCCCount > 0

Or another alternative to get all of the original records is to take the first method and use EXISTS in a correlated sub query like so:

SELECT *
FROM
    sel_pro_pmtmethod m1
WHERE
    EXISTS (SELECT 1
          FROM   sel_pro_pmtmethod m2
          WHERE
             m1.SEL_PROFILE_DETAIL_FK = m2.SEL_PROFILE_DETAIL_FK
             m2.pmt_type IN ('EFT', 'RCC')
          GROUP  BY m2.SEL_PROFILE_DETAIL_FK
          HAVING COUNT(distinct m2.pmt_type) = 2

And to address this part of your post "I need to develop unique output by SEL_PROFILE_DETAIL_FK depending on whether it has ONLY EFTs entries, ONLY RCC entries, or BOTH EFT and RCC entries." It makes it sound like you actually don't want to restrict to only when they are both but know if one or the other or both is present and you can do that with conditional aggregation like so:

SELECT
    SEL_PROFILE_DETAIL_FK
    ,CASE
       WHEN EFTCount > 0 AND RCCCount > 0 THEN 'Both'
       WHEN RCCCount > 0 THEN 'RCC'
       ELSE 'EFT'
    END as PmtTypesPresent

FROM
    (
    SELECT
       SEL_PROFILE_DETAIL_FK
       ,COUNT(CASE WHEN PMT_TYPE = 'EFT' THEN PMT_TYPE END) as EFTCount
       ,COUNT(CASE WHEN PMT_TYPE = 'RCC' THEN PMT_TYPE END) as RCCCount
    FROM   sel_pro_pmtmethod
    WHERE  pmt_type IN ('EFT', 'RCC')
    GROUP  BY SEL_PROFILE_DETAIL_FK
) t

@MarkLudlow your welcome. If you have gotten an answer you needed please accept one of them that you used so that others know you have gotten taken care of, and reputation points are awarded. Also upvoting any correct answers/answers you used is good etiquette as well. cheers. — Matt, Dec 09 '16 at 18:15

SQL - GROUP BY & HAVING COUNT issue

2 Answers2