Count function children and grandchildren, i got children how do i get grandchildren

Question

im trying to get a count of grandchildren within at dog database and only want dogs with 15 or more children and/or 40 or more grandchildren to get exported to a list. I dont need to se the names of children or grandchildren just a count of them.

I made a fiddle DB to show what i got so fare.

NEW updated 2019.08.23 kl 16:31 https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=9f700055c3d0a066b545f9f489fb1801

The database i old and set in a way that i can't change it so i have to work with what i got. I have tryed some of the way i have found on google but all of them seems to be for a more complex DB or i don't get them(most likely the last as i am not very good i sql)

if anyone has any tips or advice then they will be highly appreciated. I probably could have explained this in a better way had I known more about sql

select d2.Id ,d2.Reg , d2.Name , count(d2.id) as Children

from dbo.Dogs d 

left join dbo.Litters D0 on D0.Id = d.LitterId

left join dbo.Dogs D1 on D0.FatherId=D1.ID

left join dbo.Dogs D2 on D0.MotherId=D2.ID

Group by d2.id , d2.name , d2.Reg

having COUNT(*) > 12

this is what i got :

Id  Reg Name    Children

1   NO23456/2009    Queen   20

8   NO23457/2009    Godess  14

what i want is this

Id  Reg     Name    Children    Grandchildren

1   NO23456/2009    Queen   20      0

8   NO23457/2009    Godess  14      0

What I'm trying to accomplish here is a query where I can define a number of puppies on the mother's side and another number on the father's side. And add a third number to the grandchildren on both sides

create table dbo.dogs ( ID varchar (255), Reg varchar (255) , Name varchar (255), LitterID varchar (255));

create table dbo.litters (ID int , Dateofbirth date, FatherID int , MotherID int );

INSERT INTO Dogs (id, Reg, NAME, litterid)

VALUES ('11', 'NO34567/2012', 'Fido1', '9000'),('12', 'NO34568/2012', 'Fido2', '9000'),('13', 'NO34569/2012', 'Fido3', '9000'),('14', 'NO34570/2012', 'Fido4', '9000'),('15', 'NO34571/2012', 'Fido5', '9000'),('16', 'NO34572/2014', 'Fido6', '7000'),('17', 'NO34573/2014', 'Fido7', '7000'), ('18', 'NO34574/2014', 'Fido8', '7000'),('19', 'NO34575/2014', 'Fido9', '7000'),('20', 'NO34576/2014', 'Fido10', '7000'),('21', 'NO34577/2014', 'Fido11', '7000'),('22', 'NO34578/2016', 'Fido12', '6000'),('23', 'NO34579/2016', 'Fido13', '6000'),('24', 'NO34580/2016', 'Fido14', '6000'), ('25', 'NO34581/2016', 'Fido15', '6000'),('26', 'NO34582/2016', 'Fido16', '6000'),('27', 'NO34583/2016', 'Fido17', '6000'),('28', 'NO34584/2015', 'Fido18', '5000'),('29', 'NO34585/2015', 'Fido19', '5000'),('30', 'NO34586/2015', 'Fido20', '5000'),('31', 'NO34587/2015', 'Fido21', '5000'), ('32', 'NO34588/2015', 'Fido22', '5000'),('33', 'NO34589/2015', 'Fido23', '5000'),('34', 'NO34590/2015', 'Fido24', '5000'),('35', 'NO34591/2015', 'Fido25', '5000'),('36', 'NO34592/2015', 'Fido26', '5000'),('37', 'NO34593/2017', 'Fido27', '4000'),('38', 'NO34594/2017', 'Fido28', '4000'), ('39', 'NO34595/2017', 'Fido29', '4000'),('40', 'NO34596/2017', 'Fido30', '4000'),('41', 'NO34597/2017', 'Fido31', '4000'),('42', 'NO34598/2017', 'Fido32', '4000'),('43', 'NO34599/2017', 'Fido33', '4000'),('44', 'NO34600/2017', 'Fido34', '4000'),('45', 'NO34601/2014', 'Fido35', '3000'), ('46', 'NO34602/2014', 'Fido36', '3000'),('47', 'NO34603/2014', 'Fido37', '3000'),('48', 'NO34604/2014', 'Fido38', '3000'),('49', 'NO34605/2014', 'Fido39', '3000'),('50', 'NO34606/2014', 'Fido40', '3000'),('51', 'NO34607/2015', 'Fido41', '2000'),('52', 'NO34608/2015', 'Fido42', '2000'), ('53', 'NO34609/2015', 'Fido43', '2000'),('54', 'NO34610/2015', 'Fido44', '2000'),('55', 'NO34611/2015', 'Fido45', '2000'),('56', 'NO34631/2018', 'Fido45jr', '2001'),('57', 'NO34641/2018', 'Fido45jr', '2001'),('58', 'NO34651/2018', 'Fido45jr', '2001'), ('59', 'NO34661/2018', 'Fido45jr', '2001'),('2', 'NO12345/2010', 'king', '8001'),('1', 'NO23456/2009', 'Queen', '7001'),('7', 'NO12346/2010', 'God', '8000'),('8', 'NO23457/2009', 'Godess', '7001'),('9', 'NO12346/2010', 'Devil', '8000'),('5', 'NO12346/2011', 'Princess', '9002');

INSERT INTO Litters (ID, Dateofbirth, FatherID, MotherID)

VALUES ('9000', '2012.04.01', '2', '1'),('5000', '2015.05.01', '2', '1'),('6000', '2016.06.01', '9', '1'),('7000', '2014.04.02', '7', '8'),('4000', '2017.05.02', '2', '8'),('3000', '2014.06.02', '2', '5'),('2000', '2015.04.03', '9', '5'),('2001', '2018.04.03', '23', '55');

A fiddle link is great, but's even better to include the DDL and DML in the question itself. Also, it would be a hell of a lot easier to work with a minimized sample data. There's plenty of irrelevant data in your fiddle, we don't need that much. — Zohar Peled
Not in this example but i want the same results for male dogs (D1 = Father and D2 = Mother) — Jim

ilyazakharov ilyazakharov · Accepted Answer · 2019-08-22T16:54:51

If I understand your schema right that would work:

SELECT d.Id                   AS ParentID,
       d.Name,
       d.Reg,
       COUNT(DISTINCT d1.id)  AS children,
       COUNT(DISTINCT d2.id)  AS grandchildren
FROM   Dogs d
       JOIN dbo.Litters LittersFarther
            ON  LittersFarther.FatherId = d.id
                OR LittersFarther.MotherId = d.id
       LEFT JOIN Dogs d1
            ON  LittersFarther.id = d1.litterid
       LEFT JOIN dbo.Litters Litters2
            ON  Litters2.FatherId = d1.id
                OR Litters2.MotherId = d1.id
       LEFT JOIN Dogs d2
            ON  Litters2.id = d2.litterid
       LEFT JOIN dbo.Litters Litters1
            ON  Litters1.id = d.litterID
WHERE  Litters1.id IS            NULL
GROUP BY
       d.Id,
       d.Name,
       d.Reg
HAVING COUNT(DISTINCT case when LittersFarther.FatherId = d.id then d1.id ELSE 0 end) > 12 -- Filter for number of father's children
       OR COUNT(DISTINCT case when LittersFarther.FatherId = d.id then d2.id ELSE 0 end) > 2  -- Filter for number of father's grandchildren   
       OR COUNT(DISTINCT case when LittersFarther.MotherId = d.id then d1.id ELSE 0 end) > 12  -- Filter for number of mother's children
       OR COUNT(DISTINCT case when LittersFarther.MotherId = d.id then d2.id ELSE 0 end) > 4  -- Filter for number of mother's grandchildren

Count function children and grandchildren, i got children how do i get grandchildren

1 Answers