SQLTeam.com | Weblogs | Forums

Simple Grouping query help

If i have a table like this:

Owner name, Dog name, Vaccine
Didi, Bruce, Yes
Didi, Dan, No
Didi, Dagi, Yes
Guy, Mika, Yes
Guy, Blue, No

and I want this result :
Owner name, Number of dogs, Number of dogs with vaccine,Number of dogs without vaccine

How should I do it ?

Thanks

hi

i have written this SQL ... please see if its what you want ..

if you want to learn how to write SQL that's something else

select 
    ' my SQL Output '
   ,  Ownername
   , Count(DogName) as NumberOfDogs
   , sum(case when Vaccine = 'Yes' then 1 else 0 end ) as DogsWithVaccine
   , sum(case when Vaccine = 'No'  then 1 else 0 end ) as DogsWithoutVaccine 
from 
   @Starting_Data
group by 
   Ownername


declare @Starting_Data table (Ownername varchar(200), Dogname varchar(200) , Vaccine varchar(200) ) 

insert into @Starting_Data Values 
('Didi','Bruce ','Yes'),
('Didi','Dan   ','No'),
('Didi','Dagi  ','Yes'),
('Guy','Mika   ','Yes'),
('Guy','Blue   ','No')
2 Likes

Nice & clean approach .
Helped me a lot.

Thank you !