How to return records in Group by a column value in SQL Server?

Updated on August 11, 2015     Kisan Patel

This tutorial will explain you how to return records in Group by a column value with HAVING and WHERE clause in SQL Server?

To return records from the database based on group by a certain columns, we use Group by keyword. This is generally done by those columns whose values are repetitive in nature.

To demonstrate this, we have joined Orders and Customers table from Northwinddatabase.

Now write following sql statement in the query window.

SELECT O.CustomerID, C.ContactName, COUNT(O.OrderID) 
FROM Customers as C INNER JOIN Orders as O ON O.CustomerID = C.CustomerID
GROUP BY O.CustomerID, C.ContactName

Note: It is important to have the columns that are being used as Group by in the SELECT statement.

With HAVING Clause

SELECT O.CustomerID, C.ContactName, COUNT(O.OrderID) FROM Customers as C INNER JOIN Orders as O ON O.CustomerID = C.CustomerID
GROUP BY O.CustomerID, C.ContactName
HAVING COUNT(O.OrderID) > 20

Note: The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

With WHERE Clause

SELECT O.CustomerID, C.ContactName, COUNT(O.OrderID) FROM Customers as C INNER JOIN Orders as O ON O.CustomerID = C.CustomerID
WHERE O.CustomerID = 'QUICK'
GROUP BY O.CustomerID, C.ContactName
HAVING COUNT(O.OrderID) > 20