SQL中的Union 2运行平衡

Question

当我单独执行这些查询时，运行总数和运行平均值是正确的，但联合运行总数和运行平均值是不正确的计算。

不明白union里发生了什么。

 SELECT BillDate,Location,Branch,Quantity,Amount,NoOfTransaction,BillYear,Item, 
    SUM(Amount) over(order by BillDate rows unbounded preceding) as RunningTotal,
    AVG(Amount) over(order by BillDate rows unbounded preceding) as RunningAverage
    FROM vwLocationWiseSales
    WHERE  YEAR(BillDate) = YEAR(GETDATE())
    UNION
    SELECT BillDate,Location,Branch,Quantity,Amount,NoOfTransaction,BillYear,Item, 
    SUM(Amount) over(order by BillDate rows unbounded preceding) as RunningTotal,
    AVG(Amount) over(order by BillDate rows unbounded preceding) as RunningAverage
    FROM vwLocationWiseSales
    WHERE YEAR(BillDate) = YEAR(DATEADD(YEAR,-1,GETDATE()))

示例数据

2015-01-01 00:00:00.000 CHICKEN                                            Bahadurabad - Floor                                9.248                                   3782.71                                 4               2015        WHOLE CHICKEN                                                                                                                                                                                                                                                                                    
2015-01-01 00:00:00.000 CHICKEN                                            Binori Town - Floor                                5.560                                   2298.27                                 4               2015        WHOLE CHICKEN                                                                                                                                                                                                                                                                                   
2015-01-01 00:00:00.000 CHICKEN                                            Boat Basin-Floor                                   1.634                                   678.11                                  2               2015        WHOLE CHICKEN                                                                                                                                                                                                                                                                                   
2016-01-31 00:00:00.000 CHICKEN                                            Phase 2 Commercial-Floor                           17.374                                  5994.03                                 17              2016        CHICKEN KARHAI CUT ( 18 PIECES )                                                                                                                                                                                                                                                           
2016-01-31 00:00:00.000 CHICKEN                                            SEHER - Floor                                      6.142                                   3249.12                                 7               2016        CHICKEN MINCE                                                                                                                                                                                                                                                                              
2016-01-31 00:00:00.000 CHICKEN                                            SEHER - Floor                                      0.006                                   2.02                                    1               2016        WHOLE CHICKEN

需要输出

2015-01-01 00:00:00.000 CHICKEN                                            Bahadurabad - Floor                                9.248                                   3782.71                                 4               2015        WHOLE CHICKEN                                                                                                                                                                                                                                                   3782.71                                 
2015-01-01 00:00:00.000 CHICKEN                                            Binori Town - Floor                                5.560                                   2298.27                                 4               2015        WHOLE CHICKEN                                                                                                                                                                                                                                                   6080.98                                 
2015-01-01 00:00:00.000 CHICKEN                                            Boat Basin-Floor                                   1.634                                   678.11                                  2               2015        WHOLE CHICKEN                                                                                                                                                                                                                                                   6759.09                                 
2016-01-31 00:00:00.000 CHICKEN                                            Phase 2 Commercial-Floor                           17.374                                  5994.03                                 17              2016        CHICKEN KARHAI CUT ( 18 PIECES )                                                                                                                                                                                                                                5994.03                            
2016-01-31 00:00:00.000 CHICKEN                                            SEHER - Floor                                      6.142                                   3249.12                                 7               2016        CHICKEN MINCE                                                                                                                                                                                                                                                   9243.15                            
2016-01-31 00:00:00.000 CHICKEN                                            SEHER - Floor                                      0.006                                   2.02                                    1               2016        WHOLE CHICKEN                                                                                                                                                                                                                                                   9245.17                            

Answer 1

尝试使用一个查询:

SELECT BillDate, Location, Branch, Quantity, Amount, NoOfTransaction, 
       BillYear,Item, 
       SUM(Amount) over(partition by YEAR(BillDate)
                        order by BillDate rows unbounded preceding) as RunningTotal,
       AVG(Amount) over(partition by YEAR(BillDate)
                        order by BillDate rows unbounded preceding) as RunningAverage
    FROM vwLocationWiseSales
    WHERE  YEAR(BillDate) IN (YEAR(DATEADD(YEAR,-1,GETDATE())),  YEAR(GETDATE()))