按季度分组（日期部分）返回同一季度的多个行 [英] Group by quarter (datepart) returns multiple rows with same quarter

问题描述

我试图返回历史记录表中按季度和年份分组的记录总数的计数。目前，我有：

I'm trying to return a count for the total number of records in the table HISTORY grouped by their quarter and year. Currently I have:

SELECT DISTINCT (CAST(DATEPART(year, CREATE_DATE) AS char) + ' Qtr' + 
                CAST(DATEPART(quarter, CREATE_DATE) AS char)) AS Period,
       COUNT(ID)
FROM HISTORY
GROUP BY CREATE_DATE
ORDER BY Period;

但是我得到了具有相同季度和年份的重复行。我还得到了总数低于表中总记录数的记录。 这里是一个示例SQL小提琴，以帮助识别问题。

But I'm getting duplicate rows with the same quarter and year. I'm also getting a total of records counted that's lower than the total records in the table. Here's a sample sql fiddle in case that helps identify the problem.

我也不会认为我需要在period列中指定DISTINCT，但是当我不这样做时，我会得到更多的重复...我想这是其中的一部分

I wouldn't have thought I'd need to specify DISTINCT in the period column either, but when I don't I get even more dupes... which I'm guessing is part of the same root problem.

推荐答案

问题在于您要按CREATE_DATE进行分组，但要按年份和季度进行分组：

The problem ist that you are grouping by CREATE_DATE but want to group by year and quarter:

SELECT 
  DATENAME(year, CREATE_DATE) + ' Qtr' + DATENAME(quarter, CREATE_DATE) AS Period,
  COUNT(*) AS NumberOfRecords
FROM HISTORY
GROUP BY DATENAME(year, CREATE_DATE), DATENAME(quarter, CREATE_DATE)
ORDER BY Period;

这篇关于按季度分组（日期部分）返回同一季度的多个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！