如果另一列不在SQL Server中退出某个值,则按季度获取行和总计组的计数 [英] Get count of row and sum group by quarter of date, if another column doesnt exits a value in SQL Server
问题描述
我有一些样本数据:
日期状态OfferNum金额
--------- ---------------------------------------------
2016 / 10/30 - 1 - 2000 - 1000,00
2016/08/25 - 0 - 2000 - 1100,00
2016/07/12 - 0 - 2001 - 1200,00
2016/08/30 - 0 - 2001 - 1300,00
2016/07/12 - 1 - 2002 - 1400,00
2016/08/30 - 1 - 2002 - 1500,00
2016/08/30 - 1 - 2003 - 1600,00
我不想数如果其中一个 offerNum
状态值的值为1,并且在同一季度(如果它有1个,但它不是同一个季度,则必须计数)。但我想总结所有的金额(它不依赖于状态栏)
这是我想要的结果:
Quarter Count TotalAmount
------------------------------ ----------------------
2016 / Q3 2(offerNum 2002 and 2003)8100,00
2016 / Q4 1(offerNum 2000 )1000,00
以下是sqlfiddle: http://sqlfiddle.com/#!6/eac9d
offer
的状态,然后计算按季度汇总的最终结果。请注意, GROUP BY
年非常重要,否则结果将包含来自前几年同一季度的数据。 -
- 根据SQL小提琴更新答案。
- 检查:http://sqlfiddle.com/#!6/709ff/9
-
WITH优惠AS
(
SELECT
CONCAT(DATEPART(yy,date),'/ Q',DATEPART(qq,date))AS季度,
报价,
MAX(状态)AS状态,
SUM(金额)AS TotalAmount
FROM temp
GROUP BY
DATEPART(yy,date),
DATEPART(qq,date),
offer
)
SELECT
Quarter,
SUM(status)AS Count,
SUM(TotalAmount)AS TotalAmount $ b $ FROM offer
GROUP BY Quarter
I have some sample data:
Date Status OfferNum Amount
------------------------------------------------------
2016/10/30 - 1 - 2000 - 1000,00
2016/08/25 - 0 - 2000 - 1100,00
2016/07/12 - 0 - 2001 - 1200,00
2016/08/30 - 0 - 2001 - 1300,00
2016/07/12 - 1 - 2002 - 1400,00
2016/08/30 - 1 - 2002 - 1500,00
2016/08/30 - 1 - 2003 - 1600,00
I don't want to count if one of offerNum
status value has 1 and in the same quarter(if it has 1 but it isnt same quarter it has to be count). But I want to sum all of the amount(it isnt depends status column)
Here is the result that I want:
Quarter Count TotalAmount
----------------------------------------------------
2016/Q3 2 (offerNum 2002 and 2003) 8100,00
2016/Q4 1 (offerNum 2000) 1000,00
Here is the sqlfiddle : http://sqlfiddle.com/#!6/eac9d
You can use a subquery to compute the status of each offer
, then compute the final result aggregated by quarter. Notice that the GROUP BY
year is important, otherwise result will contain data coming from the same quarter of the previous years.
--
-- Answer updated according to SQL Fiddle.
-- Check: http://sqlfiddle.com/#!6/709ff/9
--
WITH offers AS
(
SELECT
CONCAT(DATEPART(yy, date), '/Q', DATEPART(qq, date)) AS Quarter,
offer,
MAX(status) AS status,
SUM(amount) AS TotalAmount
FROM temp
GROUP BY
DATEPART(yy, date),
DATEPART(qq, date),
offer
)
SELECT
Quarter,
SUM(status) AS Count,
SUM(TotalAmount) AS TotalAmount
FROM offers
GROUP BY Quarter
这篇关于如果另一列不在SQL Server中退出某个值,则按季度获取行和总计组的计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!