469,090 Members | 1,163 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

rating system

this is probably a simple sql solution, but i could use some help.
i've got one table that has rows of documents, and another table with
any number of ratings for each of the documents. The rating table is
linked with an id column. the issue i am running up against is
returning a single average rating for a document.

SELECT document.title, (SELECT AVG(scale)
FROM Rating, document
WHERE
document.pkDocumentId = rating.documentId)
FROM Document

returns the same avg for all the ratings.

So i'd ideally like to return the values of the document columns
(tableA.*), with an additional column containing the average rating of
that document (avg(tableB.Scale) where pkDocumentId = documentId).

thanks in advance.
Jul 20 '05 #1
1 1500
SELECT D.title, AVG(R.scale)
FROM Document AS D
JOIN Rating AS R
ON D.pkdocumentid = R.documentid
GROUP BY D.pkdocumentid, D.title

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by alex | last post: by
4 posts views Thread by Amir Ghezelbash | last post: by
4 posts views Thread by bp90210 | last post: by
3 posts views Thread by cbtube03 | last post: by
pradeepjain
1 post views Thread by pradeepjain | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.