CODEWITHSUNDEEP
sql-server
Ginger22
Ginger22

Reputation: 157

Aggregating Group By in Subquery

Trying to return Sales by Year per particular Sales Person in one column and a percentage of Total Sales in next column.

The subquery should return the grand total of all sales, so a percentage can be calculated. But the percentage is wrong, because I'm not able to aggregate the OrderDate because then it won't be recognized in the join statement(Not able to group by YEAR(OrderDate). Any insights would be great. 

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)

Upvotes: 0

Views: 38

Answers (1)

forpas
forpas

Reputation: 164089

With group by year(orderdate) get the sales of the salespersonid = 275 and the yearlygrandtotal and use it as a subquery to calculate the percentage:

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

Upvotes: 1

Related Questions