GROUP BY is use to make the grouping rows which having similar values.
Most of the time GROUP BY is going to used with Aggregate Functions.


Let consider below list of student with there class, marks, city and contact details.

select statement for group by example


GROUP BY in SQL Server

Find out the count of student from each city.
Let see below example.

Example

SELECT COUNT (StudentName) AS 'StudentCount' , City
FROM tblStudent
GROUP BY City

Output

group by example in sql server


GROUP BY on multiple columns in SQL Server

Find out the average marks of student from each city and class.
Let see below example.

Example

SELECT AVG (Marks) AS 'AvarageMark' City, Class
FROM tblStudent WHERE City= 'Mumbai'
GROUP BY City , Class
ORDER BY City

Output

group by example with multiple columns in sql server


Note : When we use the multiple columns in select statement that columns must have after GROUP BY keyword.
Because select make the groping according to specified column so that should be after GROUP BY keyword.
In above example we use the grouping on City, Class so we use them in select statement after AsssvarageMark column and after GROUP BY keyword.


If selected columns and GROUP BY columns get mismatch then SQL Server will give an below error.
Let see below example.

Example

SELECT AVG (Marks) AS 'AvarageMark' City, Class
FROM tblStudent WHERE City= 'Mumbai'
GROUP BY City
ORDER BY City

Output

invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause


Error : Column 'tblStudent.Class' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Solution, Use the Class columns after GROUP BY with City columns

Example

SELECT AVG (Marks) AS 'AvarageMark' City, Class
FROM tblStudent WHERE City= 'Mumbai'
GROUP BY City, Class
ORDER BY City

Output

invalid in the select list because it is not contained in
        either an aggregate function or the GROUP BY clause solution