时间戳

首页 > 解决方案 > 在子查询中聚合分组依据

sql-server - 在子查询中聚合分组依据

问题描述

尝试在一列中返回每个特定销售人员的年度销售额，并在下一列中返回总销售额的百分比。

子查询应返回所有销售额的总计，因此可以计算百分比。但是百分比是错误的，因为我无法汇总 OrderDate，因为这样它就不会在 join 语句中被识别（无法按 YEAR（OrderDate）分组。任何见解都会很棒。 

SELECT Year(t1.OrderDate) as SalesYear,   sum(SubTotal+TaxAmt) as Sales,
    sum(SubTotal+TaxAmt)  / sum(YearlyGrandTotal) AS [Percent]
 FROM [AdventureWorks2017].[Sales].[SalesOrderHeader] t1
INNER JOIN (
    SELECT  OrderDate,Year(OrderDate) as O, sum(SubTotal+TaxAmt) as 
YearlyGrandTotal
    FROM Sales.SalesOrderHeader  
    GROUP BY (OrderDate)
) AS [Total] ON t1.OrderDate = Total.OrderDate
where SalesPersonID = 275

GROUP BY Year(t1.OrderDate)

标签： sql-server

解决方案

使用group by year(orderdate)get thesales和salespersonid = 275theyearlygrandtotal并将其用作子查询来计算百分比：

select 
  t.salesyear,
  t.sales,
  100.0 * t.sales / t.yearlygrandtotal percentage
from (
  select 
    year(orderdate) salesyear,
    sum(case when salespersonid = 275 then subtotal + taxamt else 0 end) sales,
    sum(subtotal + taxamt) yearlygrandtotal
  from [AdventureWorks2017].[Sales].[SalesOrderHeader]
  group by year(orderdate) 
) t

推荐阅读