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)

Hi all,

I have managed to get my average using cross tabs but only if I have grouped it by their categories. If I remove the GROUP BY clause, the cross tabbing doesn’t seem to work. Can anyone help me? I would need to get something like:-

Year | Month | Benefits Rating | Facilities Rating
—————————————————–
2007 | December | 2 | 3
2008 | November | 3 | 3
2008 | October  | 2 | 1
2008 | September| 2 | 2

Thanks

p/s: example is shown as an image below.

Attachment 8641

Attached Images

sql.PNG (16.7 KB)

I am facing some kind of problem. The error I am getting it’s look like
"Errorno = 2003
Error Message = Can’t connect to MySQL server on ’server IP address’
(99)".

This error is coming most of the time.

1) the server is also running when I got this error.

2) I checked the config file, there is no parameter defined for the mysql port, it might be using the default port no 3306.

3) I have checked the server with telnet also, I can connect to the server with the default port no, it’s showing connected.

Can anybody help me …

Hey how will I write a text file to setup a database with the information below

Database set-up:

Members Table:
id - int primary key auto_increment.
username - varchar 15.
password - varchar 32. will be a md5 and sha1 hash of the password then the username.
e-mail - varchar 35.
admin - tinyint. will either be a value of 1 (admin) or the default, 0 (regular member)
last_ip - varchar 15. will store the last ip address person logged in with.

Buddy List:
uid - User ID from members table. int.
buddy - Buddies username or id. either varchar 15 or int.
blocked - Blocked users username or id. either varchar 15 or int.

Messages:
id - message id. int primary key auto_increment.
from - users name. varchar 15 characters.
to - buddies name. varchar 15 characters.
message - blob
time - timestamp CURRENT_TIMESTAMP.