none
Count from another table based on a criteria RRS feed

  • Question

  • How do I return the following output from Table1 and Table2

    Output
    Patient  PatientID      Minors
    John 55555 1
    Mike 77777 1
    Julio 99999 3
    Tim 22222 0
    Rick 66666 0


    Table1
    Patient PatientID
    John 55555
    Mike 77777
    Julio 99999
    Tim 22222
    Rick 66666
    Table2
    ResponsibleParty PatientID Relationship Age
    John 55555 Child 18
    John 55555 Self 45
    Mike 77777 Self 33
    Mike 77777 Child 15
    Mike 77777 Self 38
    Julio 99999 Self 51
    Julio 99999 Child 11
    Julio 99999 Child 12
    Julio 99999 Child 13
    Julio 99999 Self 58
    Tim 22222 Self 29
    Tim 22222 Self 21

    Tuesday, January 14, 2020 11:12 PM

Answers

  • Create Table Table1(Patient varchar(10),	PatientID  int)
    Insert Table1(Patient, PatientID) Values 
    ('John', 	55555),
    ('Mike', 	77777),
    ('Julio', 	99999),
    ('Tim', 	22222),
    ('Rick', 	66666);
    Create Table Table2(ResponsibleParty varchar(10),	PatientID int,	Relationship varchar(10),	Age int);
    Insert Table2(ResponsibleParty, PatientID, Relationship, Age) Values
    ('John', 	55555, 	'Child', 	18),
    ('John', 	55555, 	'Self', 	45),
    ('Mike', 	77777, 	'Self', 	33),
    ('Mike', 	77777, 	'Child', 	15),
    ('Mike', 	77777, 	'Self', 	38),
    ('Julio', 	99999, 	'Self', 	51),
    ('Julio', 	99999, 	'Child', 	11),
    ('Julio', 	99999, 	'Child', 	12),
    ('Julio', 	99999, 	'Child', 	13),
    ('Julio', 	99999, 	'Self', 	58),
    ('Tim', 	22222, 	'Self', 	29),
    ('Tim', 	22222, 	'Self', 	21);
    
    Select t1.Patient, t1.PatientID, Count(Case When t2.Relationship = 'Child' Then 1 Else NULL End) As Minors
    From Table1 t1
    Left Join Table2 t2 On t1.PatientID = t2.PatientID
    Group By t1.Patient, t1.PatientID;
    

    Tom
    • Marked as answer by MikeGanem2 Wednesday, January 15, 2020 1:12 AM
    Wednesday, January 15, 2020 12:11 AM
  • please run below code if you want to eliminate duplicates 


    select t1.Patient , t1.PatientID , sum(Case t2.Relationship when 'Child' then 1 else 0 end) as Minors 

    from #Table1 t1 

    left join (SELECT DISTINCT ResponsibleParty, PatientID, Relationship, Age FROM #Table2) t2

    on t1. PatientID= t2.PatientID

    group by t1.Patient , t1.PatientID

    also , this code is counting if the Patient has one 'Child'

    • Marked as answer by MikeGanem2 Wednesday, January 15, 2020 1:12 AM
    Wednesday, January 15, 2020 12:50 AM

All replies

  • Create Table Table1(Patient varchar(10),	PatientID  int)
    Insert Table1(Patient, PatientID) Values 
    ('John', 	55555),
    ('Mike', 	77777),
    ('Julio', 	99999),
    ('Tim', 	22222),
    ('Rick', 	66666);
    Create Table Table2(ResponsibleParty varchar(10),	PatientID int,	Relationship varchar(10),	Age int);
    Insert Table2(ResponsibleParty, PatientID, Relationship, Age) Values
    ('John', 	55555, 	'Child', 	18),
    ('John', 	55555, 	'Self', 	45),
    ('Mike', 	77777, 	'Self', 	33),
    ('Mike', 	77777, 	'Child', 	15),
    ('Mike', 	77777, 	'Self', 	38),
    ('Julio', 	99999, 	'Self', 	51),
    ('Julio', 	99999, 	'Child', 	11),
    ('Julio', 	99999, 	'Child', 	12),
    ('Julio', 	99999, 	'Child', 	13),
    ('Julio', 	99999, 	'Self', 	58),
    ('Tim', 	22222, 	'Self', 	29),
    ('Tim', 	22222, 	'Self', 	21);
    
    Select t1.Patient, t1.PatientID, Count(Case When t2.Relationship = 'Child' Then 1 Else NULL End) As Minors
    From Table1 t1
    Left Join Table2 t2 On t1.PatientID = t2.PatientID
    Group By t1.Patient, t1.PatientID;
    

    Tom
    • Marked as answer by MikeGanem2 Wednesday, January 15, 2020 1:12 AM
    Wednesday, January 15, 2020 12:11 AM
  • Please try this 

    CREATE TABLE #Table1
    (Patient VARCHAR(10),
    PatientID INT)

    INSERT INTO #Table1 (Patient, PatientID)
    VALUES('John', 55555),
          ('Mike', 77777),
          ('Julio', 99999),
          ('Tim', 22222),
          ('Rick', 66666)



    CREATE TABLE #Table2
    (ResponsibleParty VARCHAR(10),
    PatientID INT,
    Relationship VARCHAR(10),
    Age TINYINT)


    INSERT INTO #Table2
    VALUES ('John',55555,'Child',18),
           ('John',55555,'Self',45),
           ('Mike',77777,'Self',33),
           ('Mike',77777,'Child',15),
           ('Mike',77777,'Self',38),
           ('Julio',99999,'Self',51),
           ('Julio',99999,'Child',11),
           ('Julio',99999,'Child',12),
           ('Julio',99999,'Child',13),
           ('Julio',99999,'Self',58),
           ('Tim',22222,'Self',29),
           ('Tim',22222,'Self',21)




    select t1.Patient , t1.PatientID , sum(Case t2.Relationship when 'Child' then 1 else 0 end) as Minors 

    from #Table1 t1 

    left join #Table2 t2

    on t1. PatientID= t2.PatientID

    group by t1.Patient , t1.PatientID

    Thanks

    Mark it as answer if it resolves your query.

    Wednesday, January 15, 2020 12:12 AM
  • Thanks but having 2 issues

    1.  It is not counting record is Minor value is 1

    2. I have some duplicate records in Table2, how do I get it to count as only 1 record?

    For Example

    ('John', 55555, 'Child', 18)
    ('John', 55555, 'Child', 18)


    • Edited by MikeGanem2 Wednesday, January 15, 2020 12:27 AM
    Wednesday, January 15, 2020 12:23 AM
  • please run below code if you want to eliminate duplicates 


    select t1.Patient , t1.PatientID , sum(Case t2.Relationship when 'Child' then 1 else 0 end) as Minors 

    from #Table1 t1 

    left join (SELECT DISTINCT ResponsibleParty, PatientID, Relationship, Age FROM #Table2) t2

    on t1. PatientID= t2.PatientID

    group by t1.Patient , t1.PatientID

    also , this code is counting if the Patient has one 'Child'

    • Marked as answer by MikeGanem2 Wednesday, January 15, 2020 1:12 AM
    Wednesday, January 15, 2020 12:50 AM
  • It needs to filter based on the age <= 18
    Wednesday, January 15, 2020 12:59 AM
  • thanks you all, it worked with the following change

    sum(Case WHEN t2.Age <=18 then 1 else 0 end)

    Wednesday, January 15, 2020 1:12 AM