返回的记录数

如何通过查询计算组返回的记录数,

例如:

select count(*)
from temptable
group by column_1, column_2, column_3, column_4

给我,

1
1
2

我需要计算以上记录得到1 + 1 + 1 = 3。

354428 次浏览
小开

最简单的解决方案是使用派生表:

Select Count(*)
From    (
Select ...
From TempTable
Group By column_1, column_2, column_3, column_4
) As Z

另一个解决方案是使用 Count District:

Select ...
, ( Select Count( Distinct column_1, column_2, column_3, column_4 )
From TempTable ) As CountOfItems
From TempTable
Group By column_1, column_2, column_3, column_4
小开

这个怎么样:

SELECT count(column_1)
FROM
(SELECT * FROM temptable
GROUP BY column_1, column_2, column_3, column_4) AS Records
小开

你可以这样做:

select sum(counts) total_records from (
select count(*) as counts
from temptable
group by column_1, column_2, column_3, column_4
) as tmp
小开
最佳答案

可以在一个查询中使用另一个 COUNT 上的 OVER 子句同时执行这两个操作

select
count(*) RecordsPerGroup,
COUNT(*) OVER () AS TotalRecords
from temptable
group by column_1, column_2, column_3, column_4
小开

我知道现在很晚了，但没人建议这么做:

select count ( distinct column_1, column_2, column_3, column_4)
from   temptable

这至少在 Oracle 中是可行的——我目前没有其他数据库可以测试它，而且我对 T-Sql 和 MySQL 语法也不太熟悉。

另外，我不完全确定这样做在解析器中是否更有效，或者嵌套 select 语句的其他人的解决方案是否更好。但是从编码的角度来看，我发现这个版本更加优雅。

小开

慢性创伤性脑病(CTE)对我有效:

with cte as (
select 1 col1
from temptable
group by column_1
)


select COUNT(col1)
from cte;
小开

在 PostgreSQL 中，这对我很有用:

select count(count.counts)
from
(select count(*) as counts
from table
group by concept) as count;
小开

我试图在没有子查询的情况下实现相同的目标，并且能够得到如下所需的结果

SELECT DISTINCT COUNT(*) OVER () AS TotalRecords
FROM temptable
GROUP BY column_1, column_2, column_3, column_4
小开

可以执行下面的代码吗。

SELECT COUNT(COUNT(*))
FROM temptable
GROUP BY column_1, column_2, column_3, column_4
小开

您也可以通过下面的查询获得

select column_group_by,count(*) as Coulm_name_to_be_displayed from Table group by Column;


-- For example:
select city,count(*) AS Count from people group by city
小开

试试这个查询:

select top 1 TotalRows = count(*) over ()
from yourTable
group by column1, column2
小开

如何在 SQLServer 中使用 COUNTOver (PARTITION BY { column to group BY })分区函数？

例如，如果希望按 ItemID 对产品销售进行分组，并希望对每个不同的 ItemID 进行计数，只需使用:

SELECT
{columns you want} ,
COUNT(ItemID) OVER (PARTITION BY ItemID) as BandedItemCount ,
{more columns you want}... ,
FROM {MyTable}

如果您使用这种方法，您可以不使用 GROUP BY ——假设您想返回整个列表(正如您可能需要在不显示整个数据集(即 ReportingServices)的情况下知道要带入的项的整个计数的情况下进行报表带状)。

小开

对于 预备，FirstField 可以有多个值:

select *
, concat(cast(cast((ThirdTable.Total_Records_in_Group * 100 / ThirdTable.Total_Records_in_baseTable) as DECIMAL(5,2)) as varchar), '%') PERCENTage
from
(
SELECT FirstTable.FirstField, FirstTable.SecondField, SecondTable.Total_Records_in_baseTable, count(*) Total_Records_in_Group
FROM BaseTable FirstTable
JOIN (
SELECT FK1, count(*) AS Total_Records_in_baseTable
FROM BaseTable
GROUP BY FK1
) SecondTable
ON FirstTable.FirstField = SecondTable.FK1
GROUP BY FirstTable.FirstField, FirstTable.SecondField, SecondTable.Total_Records_in_baseTable
ORDER BY FirstTable.FirstField, FirstTable.SecondField
) ThirdTable
小开

这就是我所做的

SELECT COUNT(OwnerID) FROM (SELECT OwnerID FROM #resultsTable GROUP BY OwnerID) AS c

提交答案