How do i do a left join of two tables and exclude out the row with max count -

create view servicepopularityview select dog.petid,        dog.petname [name],        dog.petbreed [breed],        dog.petdateofbirth [date of birth],        dog.petgender [gender],        dog.ownerid,        count(groominghistory.serviceid) [count] dog left join groominghistory on dog.petid= groominghistory.petid group dog.petid,          dog.petname,          dog.petbreed,          dog.petdateofbirth,          dog.petgender,          dog.ownerid; 

i have tried multiple ways found online has given me aggregate errors or cannot use clause.

i wish find way left join tables leaving me without row has common serviceid used.

there multiple "dogs" share each service.

i beginner @ this.

create view servicepopularityview select dog.petid,        dog.petname [name],        dog.petbreed [breed],        dog.petdateofbirth [date of birth],        dog.petgender [gender],        dog.ownerid,        count(groominghistory.serviceid) [count] dog left join groominghistory on dog.petid = groominghistory.petid group dog.petid,          dog.petname,          dog.petbreed,          dog.petdateofbirth,          dog.petgender,          dog.ownerid; having count(groominghistory.serviceid) <        (select top 1 count(groominghistory.serviceid)         dog         left join groominghistory on dog.petid = groominghistory.petid         group dog.petid,                  dog.petname,                  dog.petbreed,                  dog.petdateofbirth,                  dog.petgender,                  dog.ownerid         order count(groominghistory.serviceid) desc)