MS SQL

MS SQL RSS

Add SUM and AVG together also SUM and COUNT together

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Dec 2008

Posts: 1

Reputation: lixid is an unknown quantity at this point

Solved Threads: 0

lixid

Offline

Newbie Poster

Add SUM and AVG together also SUM and COUNT together

Dec 23rd, 2008

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.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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)
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)