I am having a problem with my sql select statement below. What i am trying to accomplish is add a weighted gpa average to the actual average that gets calculated after a vendor gets assigned a gpa per job. The sectioned off area is where the problem lies. it is coming up with an average that doesnt calculate properly is there another way i could write this section of code to where i could add the sum and the avg together then also add a sum and a count together to get a correct total average? It would be a great help if someone could point me in the right direction i have googled this for two days and havent found exactly what i need. Thanks in advance.
SELECT Vendors_1.VendorID AS [Vendor ID], Vendors_1.Name,
(SELECT TypeName
FROM VendorTypes
WHERE (Vendors_1.VendorTypeID = VendorTypeID)) AS VendorType1,
(SELECT TypeName
FROM VendorTypes AS VendorTypes_3
WHERE (Vendors_1.VendorTypeID2 = VendorTypeID)) AS VendorType2,
(SELECT TypeName
FROM VendorTypes AS VendorTypes_2
WHERE (Vendors_1.VendorTypeID3 = VendorTypeID)) AS VendorType3,
(SELECT COUNT(*) AS Expr1
FROM BidInvites AS BI1
WHERE (VendorID = Vendors_1.VendorID) AND (Invited = 1)) AS [Bids Sent],
(SELECT COUNT(*) AS Expr1
FROM BidInvites AS BI1
WHERE (VendorID = Vendors_1.VendorID) AND (Awarded = 1)) AS Awarded,
(SELECT (SUM(Vendors.InitGPA * Vendors.InitJobs) + AVG(VendorGPA.GPA)) / (SUM(Vendors.InitJobs) + COUNT(VendorGPA.GPA)) AS ttl
FROM Vendors INNER JOIN
VendorGPA ON Vendors.VendorID = VendorGPA.VendorID WHERE (VendorGPA.VendorID = Vendors_1.VendorID)) AS GPA
, Vendors_1.City, Vendors_1.State, Vendors_1.ZipCode,
Vendors_1.Phone, Vendors_1.Fax, Vendors_1.Email, Vendors_1.Mobile
FROM Vendors AS Vendors_1 INNER JOIN
VendorTypes AS VendorTypes_1 ON Vendors_1.VendorTypeID = VendorTypes_1.VendorTypeID
WHERE (Vendors_1.IsInactive <> 1) OR
(Vendors_1.IsInactive IS NULL)
