Say I have the following table in MS SQL Server: Make Model Year Honda Accord 1997 Honda Accord 1997 Honda Accord 1997 Honda Civic 2001 Honda Civic 2005 Honda Civic 2005 Toyota Corolla 2010 Nissan Pathfinder 1997 I want to be able to retrieve the count for the unique combination and get the results like the following: Make Model Year

Retrieving the distinct count across multiple columns

Say I have the following table in MS SQL Server:

Make	Model	Year
Honda	Accord	1997
Honda	Accord	1997
Honda	Accord	1997
Honda	Civic	2001
Honda	Civic	2005
Honda	Civic	2005
Toyota	Corolla	2010
Nissan	Pathfinder	1997

I want to be able to retrieve the count for the unique combination and get the results like the following:

Make	Model	Year	Count
Honda	Accord	1997	3
Honda	Civic	2001	1
Honda	Civic	2005	2
Toyota	Corolla	2010	1
Nissan	Pathfinder	1997	1

I thought that I could do use something like COUNT(DISTINCT()), but COUNT(DISTINCT()) across multiple columns isn’t valid in MS SQL Server. I feel like I have to use GROUP BY, but I’m not sure where to start.

Answer

Its just group by and count them :

select Make ,Model, Year, COUNT(*)
from your table 
group by Make ,Model, Year

Advertisement

Answer