SELECT COUNT(DISTINCT... ) error on multiple columns?

Question

I have a table, VehicleModelYear, containing columns id, year, make, and model.

The following two queries work as expected:

SELECT DISTINCT make, model FROM VehicleModelYear  SELECT COUNT(DISTINCT make) FROM VehicleModelYear 

However, this query doesn't work

SELECT COUNT(DISTINCT make, model) FROM VehicleModelYear 

It's clear the answer is the number of results returned by the first query, but just wondering what is wrong with this syntax or why it doesn't work.

Answer 1

COUNT() in SQL Server accepts the following syntax

COUNT(*) COUNT(colName) COUNT(DISTINCT colName) 

You can have a subquery which returns unique set of make and model that you can count with.

SELECT  COUNT(*) FROM         (             SELECT  DISTINCT make, model             FROM    VehicleModelYear         ) a 

The "a" at the end is not a typo. It's an alias without which SQL will give an error ERROR 1248 (42000): Every derived table must have its own alias.