خرید بک لینک

Vote count: 0

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

asked 24 secs ago

برچسب: نویسنده: استخدام کار تاريخ: شنبه 16 مرداد 1395 ساعت: 9:12

صفحه بندی