-
Nov 28th, 2020, 09:20 AM
#1
Thread Starter
Fanatic Member
SQL - manipulate group by result into one row
Hi
I have a little bit of issue here. I get the group by right but then what I need is to have one row for each case ID with a final result of whether the case is RFT or NRFT.
The Final result shall be "NRFT" if any of the rows in column B and C show "NRFT" and if all show "RFT" then column D should be "RFT".
Here is what I get:
Here is what I need:
Thanks in advance for any help.
-
Nov 29th, 2020, 02:06 AM
#2
Re: SQL - manipulate group by result into one row
try a MAX(CASE WHEN DVRFT ='RFT' AND NVRFT='RFT' THEN 0 ELSE 1 END)
this should tell you on the group level if all records of that group have DVRFT and NVRFT equal RFT (MAX is 0) or if there is any record in that group having a NRFT in one of these columns (MAX=1).
You could also SUM instead of max to count the number of non RFT records.
-
Nov 30th, 2020, 08:27 AM
#3
Re: SQL - manipulate group by result into one row
TRy something like this (shooting from the hip with no database to try it against)
Code:
select CaseId, CASE WHEN DVRFT = 'RFT' and NVRFT = 'RFT' THEN 'RFT' ELSE 'NRFT' END as FinalRFT
from _yourtable_
group by CaseId, CASE WHEN DVRFT = 'RFT' and NVRFT = 'RFT' THEN 'RFT' ELSE 'NRFT' END
I assumed SQL Server, if you're using something else, you may need to adjust accordingly.
-tg
-
Dec 2nd, 2020, 06:49 AM
#4
Re: SQL - manipulate group by result into one row
@TG, I don't think that'll work. The case is checking whether the current row contains NFRT but will miss if any row with the same Case ID has NFRT. E.g. 25663 will appear twice, once with NRFT and once without.
I think this could be accomplished with a Group By and Having. The check for NRFT is essentially an aggregate function. I think you'd probably have to write your own aggregate function though.
Personally, I think the simplest solution to this is to do it in two steps. 1. Get the list of Case IDs that have an NRFT in any row. 2. Join from the main table to the result of step 1 and use that in the case:-
sql Code:
create table #test (CaseID int, DVRFT varchar(16), NVRFT varchar(16))
insert into #test values (25663, 'RFT', 'NRFT')
insert into #test values (25663, 'RFT', 'RFT')
insert into #test values (45778, 'NRFT', 'RFT')
insert into #test values (45778, 'RFT', 'RFT')
insert into #test values (45778, 'RFT', 'RFT')
insert into #test values (25669, 'RFT', 'RFT')
insert into #test values (25669, 'RFT', 'RFT')
insert into #test values (88963, 'RFT', 'NRFT');
with cteCasesWithNRFT as
(
Select Distinct CaseID
From #test
Where DVRFT = 'NRFT'
Or NVRFT = 'NRFT'
)
Select T.CaseID,
Case When NRFT.CaseID is null then 'RFT' else 'NRFT' end
From #test T
Left Join cteCasesWithNRFT NRFT
on T.CaseID = NRFT.CaseID
Group By T.CaseID,
Case When NRFT.CaseID is null then 'RFT' else 'NRFT' end
Drop Table #test
Edit> Can I throw out a general request that people NOT post screenies of data? It makes it really hard to set up a test as we have to copy it all by hand. At the very least, post values directly into the forum so we can cut and paste them. Even better, throw a sql script together that creates an example table as I've done here. It make it sooooo much easier for us to help you and doesn't take that much effort.
Last edited by FunkyDexter; Dec 2nd, 2020 at 06:54 AM.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Dec 2nd, 2020, 08:13 AM
#5
Re: SQL - manipulate group by result into one row
Originally Posted by FunkyDexter
@TG, I don't think that'll work. The case is checking whether the current row contains NFRT but will miss if any row with the same Case ID has NFRT. E.g. 25663 will appear twice, once with NRFT and once without.
I think this could be accomplished with a Group By and Having. The check for NRFT is essentially an aggregate function. I think you'd probably have to write your own aggregate function though.
Aaaaah..... I see what you mean... that's some messed up data. I didn't look at it close enough to notice that 25663 had NFRT, RTF on one row, and RTF, RFT on another... Ick... then, yes a straight case like I used it wouldn't work. In that case, yeah using a CTE like that is probably the wiser choice.
-tg
-
Dec 2nd, 2020, 08:16 AM
#6
Re: SQL - manipulate group by result into one row
Yeah, my first thought when I saw the question was exactly the same as yours but there was this niggling doubt that I'd missed something
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Dec 2nd, 2020, 11:21 AM
#7
Re: SQL - manipulate group by result into one row
thank you funky for the test data, this is what i meant:
Code:
create table #test (CaseID int, DVRFT varchar(16), NVRFT varchar(16))
insert into #test values (25663, 'RFT', 'NRFT')
insert into #test values (25663, 'RFT', 'RFT')
insert into #test values (45778, 'NRFT', 'RFT')
insert into #test values (45778, 'RFT', 'RFT')
insert into #test values (45778, 'RFT', 'RFT')
insert into #test values (25669, 'RFT', 'RFT')
insert into #test values (25669, 'RFT', 'RFT')
insert into #test values (88963, 'RFT', 'NRFT');
SELECT CaseID,SUM(CASE WHEN DVRFT ='RFT' AND NVRFT='RFT' THEN 0 ELSE 1 END)
FROM #test
GROUP BY CaseID
Drop Table #test
it could also be turned into:
Code:
SELECT CaseID,CASE WHEN SUM(CASE WHEN DVRFT ='RFT' AND NVRFT='RFT' THEN 0 ELSE 1 END)=0 THEN 'RFT' ELSE 'NRFT' END
FROM #test
GROUP BY CaseID
to get RTF and NRTF again.
-
Dec 3rd, 2020, 03:44 AM
#8
Re: SQL - manipulate group by result into one row
Ooh, I like that sum trick. It's a little obtuse, perhaps, but it's very succinct.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|