csab csab - 6 months ago 31
SQL Question

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

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

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
Comments