如何获得季度金额的总和 [英] How to get the sum of Quarters Amounts

问题描述

我的表：

My Table:

Month	    Nr of Projects	Amount	     Ab-,Zugang
2014-05-01	      8	        3004	       Abgang
2014-02-01	      5	        2314	       Abgang
2014-11-01	     10	        1366	       Zugang
2014-01-01	     1	        37443	       Zugang
2014-07-01	     7	        18400	       Zugang 
2014-12-01	     3	        1500	       Abgang
2014-06-01	     11	        2000	       Zugang
2014-09-01	     9	        8000	       Zugang
2014-03-01	     5	        2555	       Zugang
2014-01-01	     2	        5000	       Zugang
2014-03-01	     7	        7000	       Zugang

我的期望：

My Expectation:

Month_Quarter	Zugang	Total_Amount_Zugang	 Abgang	  Total_Amount_Abgang
    Q1	          4	          51998	           1	      2314
    Q2	          1	          2000	           1	      3004
    Q3	          3	          26000            0	       0
    Q4	          1	          1366	           1	      1500

我的陈述：

My Statement:

SELECT 'Q'+cast([Month_Quarter] as varchar) Month_Quarter,COALESCE([Zugang],0) Zugang, COALESCE([Abgang],0) Abgang,[Total Amount] 
FROM
(
   SELECT DATEPART(QUARTER,MonthCol) [Month_Quarter],
          Ab_Zugang,
          Count(NoProjects) NoProjects,
          sum([Amount]) AS [Total Amount]
   FROM tblProjectData
   GROUP BY DATEPART(QUARTER,MonthCol), Ab_Zugang
 
 ) proj
 PIVOT (SUM(NoProjects) FOR Ab_Zugang IN (Zugang, Abgang)) As pvt
 ORDER BY Month_Quarter

但这是我上面的查询结果：

BUT This is what my results looks like with the query above:

Month_Quarter	Zugang	Abgang	Total_Amount
    Q1	          0	       1	  2314
    Q1	          4	       0	  51998
    Q2	          1	       0	  2000
    Q2	          0	       1	  3004
    Q3	          0	       0	  NULL
    Q3	          3	       0	  26000
    Q4	          1	       0	  1366
    Q4	          0	       1	  1500

我想要实现的目的是：我想知道每个季度有多少祖刚或阿布冈以及他们的总金额。如何编辑查询以获得期望结果？

What I'm trying to realize ist: I want to know how many Zugang or Abgang are in each Quarter and the total amount to them. How can I edit the query to have my expectation results?

推荐答案

试试这个：

Try this:

DECLARE @tmp TABLE(MonthCol	DATE,  [NoProjects] INT, [Amount] INT, [Ab_Zugang] VARCHAR(30))

INSERT INTO @tmp
VALUES('2014-05-01' , 8 , 3004 , 'Abgang'),
('2014-02-01' , 5 , 2314 , 'Abgang'),
('2014-11-01' , 10 , 1366 , 'Zugang'),
('2014-01-01' , 1 , 37443 , 'Zugang'),
('2014-07-01' , 7 , 18400 , 'Zugang'),
('2014-12-01' , 3 , 1500 , 'Abgang'),
('2014-06-01' , 11 , 2000 , 'Zugang'),
('2014-09-01' , 9 , 8000 , 'Zugang'),
('2014-03-01' , 5 , 2555 , 'Zugang'),
('2014-01-01' , 2 , 5000 , 'Zugang'),
('2014-03-01' , 7 , 7000 , 'Zugang')

SELECT [Month_Quarter] AS [Quarter], COALESCE(SUM([Zugang]),0) Zugang, SUM([ZugangAmount]) [ZugangAmount], COALESCE(SUM([Abgang]),0) Abgang, SUM([AbgangAmount]) [AbgangAmount]
FROM
(
   SELECT 'Q'+CONVERT(VARCHAR(5), DATEPART(QUARTER,MonthCol)) [Month_Quarter],
		CASE WHEN Ab_Zugang = 'Zugang' THEN COUNT(NoProjects) ELSE 0 END AS [Zugang], 
		CASE WHEN Ab_Zugang = 'Zugang' THEN SUM([Amount])  ELSE 0 END AS [ZugangAmount],
		CASE WHEN Ab_Zugang = 'Abgang' THEN COUNT(NoProjects) ELSE 0 END AS [Abgang], 
		CASE WHEN Ab_Zugang = 'Abgang' THEN SUM([Amount])  ELSE 0 END AS [AbgangAmount]
   FROM @tmp
   GROUP BY DATEPART(QUARTER,MonthCol), Ab_Zugang
 ) dt
 GROUP BY [Month_Quarter]

结果：

Result:

Quarter	Zugang	ZugangAmount	Abgang	AbgangAmount
Q1		4		51998			1		2314
Q2		1		2000			1		3004
Q3		2		26400			0		0
Q4		1		1366			1		1500

加入两个支点可以达到同样的效果：

The same result you can achieve by joining two pivots:

SELECT t1.[Quarter], t1.Zugang, t2.Zugang AS [ZugangAmount], t1.Abgang, t2.Abgang AS  [AbgangAmount]
FROM(
    SELECT [Quarter], [Abgang], [Zugang]
    FROM
    (
       SELECT 'Q'+CONVERT(VARCHAR(5), DATEPART(QUARTER,MonthCol)) [Quarter], [Ab_Zugang], NoProjects
       FROM @tmp
     ) dt1
     PIVOT(COUNT(NoProjects) FOR [Ab_Zugang] IN ([Abgang], [Zugang])) AS pvt1
) AS t1 INNER JOIN
    (
     SELECT [Quarter], [Abgang], [Zugang]
     FROM (
        SELECT 'Q'+CONVERT(VARCHAR(5), DATEPART(QUARTER,MonthCol)) AS [Quarter], [Ab_Zugang], [Amount]
        FROM @tmp
        ) dt2
     PIVOT(SUM([Amount]) FOR [Ab_Zugang] IN ([Abgang], [Zugang])) pvt2
     ) AS t2 ON t1.[Quarter] = t2.[Quarter]

这篇关于如何获得季度金额的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！