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.
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
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.