I am having issues getting the aggregate function to work with this adventureworks database example. I'm attempting to get shipping methods whose aual total due is greater than 5 million but when i uncomment number of employees and distinct number of employees, both total dues end up the same. Could anyone give me some help so i can understand what i need to do? I've tried CTE and over() but the aggregates didn't agree with me then. THANK YOU!!!!
USE AdventureWorks2014
SELECT DISTINCT S.Name AS 'ShippingMethod',
dt.OrderYear AS 'OrderYear',
dt.[Total Due] AS 'Total Due',
dt.[Average Total Due] AS 'Average Total Due',
dt.[Number Of Employees] AS 'Number Of Employees',
dt.[Distinct Number of Employees] AS 'Distinct Number of Employees'
FROM Purchasing.ShipMethod AS S
INNER join
(SELECT
s.Name AS 'ShippingMethod',
YEAR(P.OrderDate) AS 'OrderYear',
SUM(p.TotalDue) AS 'Total Due',
AVG(p.TotalDue) AS 'Average Total Due',
COUNT(p.EmployeeID) AS 'Number Of Employees',
p.VendorID AS 'Distinct Number of Employees'
FROM Purchasing.PurchaseOrderHeader AS P
ier JOIN Purchasing.ShipMethod AS S
ON P.ShipMethodID = S.ShipMethodID --AND p.[Total Due]=p.[Average Total Due]
group by s.Name, p.OrderDate, p.VendorID --,p.EmployeeID --/*p.TotalDue,*/----,p.VendorID
-- having SUM(p.TotalDue) > 5000000
)
AS dt
ON S.Name = dt.ShippingMethod --AND dt.[Total Due]=dt.[Average Total Due]
WHERE dt.[Total Due] > 5000000 and dt.OrderYear = '2014'--dt.[Total Due] BETWEEN dt.OrderYear 5000000
ORDER BY dt.[Total Due] DESC, OrderYear --dt.[Total Due] DESC--YEAR(dt.OrderYear) DESC
برچسب:
نویسنده: استخدام کار
تاريخ: شنبه
16 مرداد
1395 ساعت: 9:12