We have answer of your question!

100% solved queries, no empty question

Question: SQL - GROUP BY & HAVING COUNT issue


0

Advertisement


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.

Question author Mark-ludlow | Source

Answer


1


Advertisement


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
Answer author Matt

Advertisement


Tickanswer.com is providing the only single recommended solution of the question SQL - GROUP BY & HAVING COUNT issue under the categories i.e sql , oracle , having , . Our team of experts filter the best solution for you.

Related Search Queries:

You may also add your answer!