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.

回答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.

回答2:

Try combining them into a single field:

SELECT COUNT(DISTINCT make + ' ' + model)
FROM VehicleModelYear

回答3:

The syntax is valid SQL but has not been implemented in SQL-Server.

Try a rewrite:

; WITH cte AS
  ( SELECT DISTINCT make, model
    FROM VehicleModelYear
  )
SELECT COUNT(*) AS total
FROM cte ; 

or:

; WITH cte AS
  ( SELECT COUNT(DISTINCT model) AS cnt
    FROM VehicleModelYear
    GROUP BY make
  )
SELECT SUM(cnt) AS total
FROM cte ; 

or:

; WITH cte AS
  ( SELECT NULL AS n
    FROM VehicleModelYear
    GROUP BY make, model
  )
SELECT COUNT(*) AS total
FROM cte ; 

And finally, the 2nd and 3rd query above modified, without subqueries:

SELECT DISTINCT
    SUM(COUNT(DISTINCT model)) OVER () AS total
FROM VehicleModelYear
GROUP BY make ;

SELECT DISTINCT COUNT(*) OVER () AS total
FROM VehicleModelYear
GROUP BY make, model ;

---

Late additions by @Alexander Fedorenko:

SELECT TOP (1)
    SUM(COUNT(DISTINCT model)) OVER () AS total
FROM VehicleModelYear
GROUP BY make ;

SELECT TOP (1) COUNT(*) OVER () AS total
FROM VehicleModelYear
GROUP BY make, model ;

or:

; WITH cte AS
  ( SELECT DENSE_RANK() OVER(ORDER BY make, model) AS dr
    FROM VehicleModelYear
  )
SELECT MAX(dr) AS total
FROM cte ;