Results 1 to 8 of 8

Thread: SQL - manipulate group by result into one row

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    453

    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:
    Name:  Before.jpg
Views: 87
Size:  23.4 KB

    Here is what I need:

    Name:  Final.jpg
Views: 74
Size:  10.9 KB

    Thanks in advance for any help.

  2. #2
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,321

    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.

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,155

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,686

    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:
    1. create table #test (CaseID int, DVRFT varchar(16), NVRFT varchar(16))
    2. insert into #test values (25663, 'RFT', 'NRFT')
    3. insert into #test values (25663, 'RFT', 'RFT')
    4. insert into #test values (45778, 'NRFT', 'RFT')
    5. insert into #test values (45778, 'RFT', 'RFT')
    6. insert into #test values (45778, 'RFT', 'RFT')
    7. insert into #test values (25669, 'RFT', 'RFT')
    8. insert into #test values (25669, 'RFT', 'RFT')
    9. insert into #test values (88963, 'RFT', 'NRFT');
    10.  
    11. with cteCasesWithNRFT as
    12. (
    13.     Select Distinct CaseID
    14.     From #test
    15.     Where DVRFT = 'NRFT'
    16.     Or NVRFT = 'NRFT'
    17. )
    18. Select T.CaseID,
    19.         Case When NRFT.CaseID is null then 'RFT' else 'NRFT' end
    20. From #test T
    21. Left Join cteCasesWithNRFT NRFT
    22.     on T.CaseID = NRFT.CaseID
    23. Group By T.CaseID,
    24.         Case When NRFT.CaseID is null then 'RFT' else 'NRFT' end
    25.  
    26. 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

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,155

    Re: SQL - manipulate group by result into one row

    Quote Originally Posted by FunkyDexter View Post
    @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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,686

    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

  7. #7
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,321

    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.

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,686

    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
  •  



Click Here to Expand Forum to Full Width