Grouping by Multiple Columns in SQL

Grouping by Multiple Columns in SQL

I will explain this topic with an example.

Yesterday one of my colleague went for an Interview.

The Interviewer asked him that you have two tables Family and Members 

Family table has familyId,Headof the Family and FamilyAddress fields

whereas

In Child Table it has columns MemberID,FK_FamilyId,MemberName

so the Interviewers asked him  to calculate total no. of members for each family which includes HeadofFamily and familyaddress.

Below is the Table Structure with Data.

create table Family

(familyId nvarchar(50),

 HoF nvarchar(50),--HeadofFamily

 familyadd nvarchar(200)

 )

 create table Members

 (MemberId nvarchar(50),

  FK_FamilyId nvarchar(50),--Take values from Familyid from familytable

  MemberName nvarchar(50)

  )

--Family Info

insert into Family values(1,'rakesh','btm2'),('2','raghu','jpngr'),('3','vnk','mahalkshmi')

--FamilyMember Info.  

insert into members values('1','2','sumit'),('2','2','amit'),('3','2','mit'),('4','1','vikas'),('5','1','vijay'),('6','3','anil')

Now if you see in the family table Rakesh,Raghu and Vnk are the Head of Family.

The requirement was like TotalNo.ofMembers,HOF,FamilyADD

So below is the query using using Group by Various column.

  select COUNT(m.memberid)'No.ofMembers',f.HoF ,f.familyadd  from Members m join Family f

  on f.familyId =m.FK_FamilyId  group by f.familyId,f.HoF ,f.familyadd

No.ofMembers HoF familyadd

2              rakesh            btm2

3               raghu             jpngr

1               vnk                   mahalkshmi

Explanation-The query is giving the count of Members for each family using group by familyID In order to get  Headoffamily and Familyaddress the query has been grouped by Hof and FamilyAdd too.

Note if you will remove HOF and FamilyADD from group by clause it will throw an ERROR.

Msg 8120, Level 16, State 1, Line 2

Column 'Family.HoF' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The Reason for the error is every column written in Select query must be included in Group by clause when using select with aggregate function and group by clause, except the columnn which  is used in aggregate function ( min,max,count,avg...) .