如果另一列不在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屋！