我尝试了以下查询来选择GROUP BY查询产生的组数:
SELECT count(*)
FROM (
SELECT count(*)
FROM MyTable
WHERE Col2 = 'x'
GROUP BY Col1
)
很遗憾,此查询无效:Incorrect syntax near ')'.
。
注意:我只想得到一行(我已经找到了一个结果为n倍的解决方案,其中n=组计数)。
SELECT COUNT(*)
FROM (
SELECT value = COUNT(*)
FROM MyTable
WHERE Col2 = 'x'
GROUP BY Col1
) е
但我认为-你需要尝试这个查询-
SELECT COUNT(DISTINCT Col1)
FROM MyTable
WHERE Col2 = 'x'
SELECT count(*)
FROM (
SELECT 1 as dummy
FROM MyTable
WHERE Col2 = 'x'
GROUP BY Col1
) dt
不需要在子查询中计数行,结果无论如何都是一样的。
您必须将名称放在列和表中。。。
SELECT count(*) Total
FROM (
SELECT count(*) Groups
FROM MyTable
WHERE Col2 = 'x'
GROUP BY Col1
) a
您还可以在许多SQL引擎中使用CTE。例如
with CTE_table as
(
SELECT
count(*)
FROM MyTable
WHERE Col2 = 'x'
GROUP BY Col1
)
select
count(*)
from CTE_table