How to get quarterly data from monthly in SQL Server

i5desfxk 于 2023-02-21 发布在 SQL Server

关注(0)|答案(4)|浏览(136)

I have table where I am having monthly data like below.

Here my AvgSpeedOfAnswer column is calculated like this:

avg(SpeedOfAnswer)

Table:

Date	AvgSpeedOfAnswerMonth
7/1/2022	20.8
8/1/2022	22.6
9/1/2022	24.9

Now my requirement is I need to create a query where I can get quarterly data from above monthly table data.

I wrote a query like this:

SELECT
    'Quarterly' AS TrendType,
    DATEADD(Q, DATEDIFF(Q, 0, TrendStartdate), 0) AS TrendStartdate,
    SUM(AvgSpeedOfAnswer) 
FROM
    Month_Stats  
GROUP BY
    DATEADD(Q, DATEDIFF(Q, 0, TrendStartdate), 0) 
ORDER BY 
    DATEADD(Q, DATEDIFF(Q, 0, TrendStartdate), 0)

I am not sure what should I need to take for AvgSpeedOfAnswer .

Is it SUM(AvgSpeedOfAnswerMonth) or AVG(AvgSpeedOfAnswerMonth) or AVG(SUM(AvgSpeedOfAnswerMonth)) ?

Could anyone please suggest?

sql-server

来源：https://stackoverflow.com/questions/75496305/how-to-get-quarterly-data-from-monthly-in-sql-server

4条答案

按热度按时间

8zzbczxx1#

As others mentioned. You need to use DATEPART

SELECT 
    'Quarterly' AS TrendType,
    DATEFROMPARTS(YEAR(Date), ((DATEPART(Q, Date) - 1) * 3) + 1, 1) AS TrendStartdate,
    AVG(AvgSpeedOfAnswerMonth) AS AvgSpeedOfAnswerQuarter
FROM 
    Month_Stats
GROUP BY 
    YEAR(Date),
    DATEPART(Q, Date)
ORDER BY 
    YEAR(Date),
    DATEPART(Q, Date)

赞(0）回复(0）举报 2023-02-21

1dkrff032#

you can use DATEPART and QUARTER

CREATE TABLE tabl11
    ([Date] datetime, [AvgSpeedOfAnswerMonth] DECIMAL(10,1))
;
    
INSERT INTO tabl11
    ([Date], [AvgSpeedOfAnswerMonth])
VALUES
    ('2022-07-01 02:00:00', 20.8),
    ('2022-08-01 02:00:00', 22.6),
    ('2022-09-01 02:00:00', 24.9)
;

3 rows affected

SELECT YEAR([Date]), DATEPART(QUARTER, [Date]), SUM([AvgSpeedOfAnswerMonth]) sum_quarterly FROM tabl11
GROUP BY YEAR([Date]),DATEPART(QUARTER, [Date])

(No column name)	(No column name)	sum_quarterly
2022	3	68.3

fiddle

赞(0）回复(0）举报 2023-02-21

rlcwz9us3#

I think you need avg (AvgSpeedOfAnswerMonth) to get the quarterly average speed over the quarter. Sum(AvgSpeedOfAnswerMonth) and avg(sum(AvgSpeedOfAnswerMonth) give the same value, which is the sum of the quarterly values.

赞(0）回复(0）举报 2023-02-21

ogsagwnx4#

Use the DATEPART function with parameter QUARTER, like

select 'Quarterly' as Quarterly, date as date from yourTable  
group by DATEPART(quarter, date)  
order by DATEPART(quarter, date)

赞(0）回复(0）举报 2023-02-21

我来回答

How to get quarterly data from monthly in SQL Server

4条答案

相关问题

热门标签

最新问答