在多个列上选择COUNT(明显的…)错误?

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

我有一个表，vehicle - emodelyear，包含id、year、make和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.

很明显，答案是第一个查询返回的结果的数量，但是只是想知道这个语法有什么问题，或者为什么它不起作用。

3 个解决方案

#1

COUNT() in SQL Server accepts the following syntax

在SQL Server中，COUNT()接受以下语法

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

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

您可以有一个子查询，它返回惟一的make和模型集，您可以使用它进行计数。

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.

结尾的“a”不是一个打印错误。它是一个别名，如果没有这个别名，SQL将会产生错误1248(42000):每个派生表都必须有自己的别名。

#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.

语法是有效的SQL，但还没有在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:

最后，第2和第3个查询被修改，没有子查询:

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:

@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 ;

#1