Asp Forum - Simple Newbie Question (I hope) -- Subquery using 1 table

Tyrenta

3/21/2007 5:53:00 PM

Hi all -- I've searched but can't seem to figure this out -- trying to
write a query that returns an average of one column grouped by another
set of criteria, as in:

Table columns: item_id, rater_id, rating

Output: item_id, rater_id, rating, average rating (all raters, per
item)

Can I do this with one query rating than two? i.e. combine:

Query 1:
SELECT [table].item_id, AVG([table].rating) as AvgRating FROM [table]
GROUP BY [table].item_id

with

Query 2:
SELECT [table].item_id, [table].rater_id, [table].rating,
[query1].avgrating from table JOIN query ON table.item_id =
query.item_id

7 Answers

masri999

3/21/2007 6:01:00 PM

On Mar 21, 10:53 pm, "Tyrenta" <dougre...@gmail.com> wrote:
> Hi all -- I've searched but can't seem to figure this out -- trying to
> write a query that returns an average of one column grouped by another
> set of criteria, as in:
>
> Table columns: item_id, rater_id, rating
>
> Output: item_id, rater_id, rating, average rating (all raters, per
> item)
>
> Can I do this with one query rating than two? i.e. combine:
>
> Query 1:
> SELECT [table].item_id, AVG([table].rating) as AvgRating FROM [table]
> GROUP BY [table].item_id
>
> with
>
> Query 2:
> SELECT [table].item_id, [table].rater_id, [table].rating,
> [query1].avgrating from table JOIN query ON table.item_id =
> query.item_id

I think what you are looking for is

SELECT [table].item_id, [table].rater_id, [table].rating,
query.avgrating from table JOIN
(SELECT [table].item_id, AVG([table].rating) as AvgRating FROM [table]
GROUP BY [table].item_id ) as query ON table.item_id =
query.item_id

Hugo Kornelis

3/21/2007 7:54:00 PM

On 21 Mar 2007 10:53:08 -0700, Tyrenta wrote:

>Hi all -- I've searched but can't seem to figure this out -- trying to
>write a query that returns an average of one column grouped by another
>set of criteria, as in:
>
>Table columns: item_id, rater_id, rating
>
>Output: item_id, rater_id, rating, average rating (all raters, per
>item)
>
>Can I do this with one query rating than two? i.e. combine:
>
>Query 1:
>SELECT [table].item_id, AVG([table].rating) as AvgRating FROM [table]
>GROUP BY [table].item_id
>
>with
>
>Query 2:
>SELECT [table].item_id, [table].rater_id, [table].rating,
>[query1].avgrating from table JOIN query ON table.item_id =
>query.item_id

Hi Tyrenta,

I fail to see why you could not simply use

SELECT item_id, rater_id, AVG(rating)
FROM table
GROUP BY item_id, rater_id;

If that's not it, then please see www.aspfaq.com/5006.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Tyrenta

3/21/2007 8:55:00 PM

On Mar 21, 3:53 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On 21 Mar 2007 10:53:08 -0700, Tyrenta wrote:
>
>
>
> >Hi all -- I've searched but can't seem to figure this out -- trying to
> >write a query that returns an average of one column grouped by another
> >set of criteria, as in:
>
> >Table columns: item_id, rater_id, rating
>
> >Output: item_id, rater_id, rating, average rating (all raters, per
> >item)
>
> >Can I do this with one query rating than two? i.e. combine:
>
> >Query 1:
> >SELECT [table].item_id, AVG([table].rating) as AvgRating FROM [table]
> >GROUP BY [table].item_id
>
> >with
>
> >Query 2:
> >SELECT [table].item_id, [table].rater_id, [table].rating,
> >[query1].avgrating from table JOIN query ON table.item_id =
> >query.item_id
>
> Hi Tyrenta,
>
> I fail to see why you could not simply use
>
> SELECT item_id, rater_id, AVG(rating)
> FROM table
> GROUP BY item_id, rater_id;
>
> If that's not it, then please seewww.aspfaq.com/5006.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hug...

I'm trying to get the final column to simply sort by, and list the
average review for that item, but still see the detail

I apologize this is not in proper format --- I'm using access/develop
to test these and passing the SQL code to some PHP scripts on a
webserver -- in access I have to build a query to get average rating
per film and then insert that into the second query to get the output
I want. Since it's a PHP query, I'm trying to get it to work with one
database query. My data columns are:

ID reviewer_id item_id rating
1 2 319 5
2 14 336 6
3 14 321 10
4 14 330 6
5 14 340 7
6 14 333 4
7 17 332 4
8 14 327 7
9 14 325 7
10 17 322 4
11 14 334 5
etc.

And i'd like to have the output in the exact same format (item, rater,
rating),but sorted by average rating per item:

AvgOfrating reviewer_id item_id rating
8 14 327 7
8 16 327 9
8 17 331 8
8 16 331 8
7.5 14 321 10
7.5 16 321 5
7.5 14 324 8
7.5 16 324 7
7 14 325 7
7 17 325 7
7 14 330 6
7 16 330 8
7 14 335 7
7 16 335 7
7 14 340 7
6.5 14 336 6
6.5 16 336 7
5.5 17 329 7
5.5 16 329 4
5.5 14 332 4
5.5 16 332 7
5.5 14 334 5
5.5 17 334 6
5.5 17 337 6
5.5 16 337 5
5 2 319 5
5 14 328 5
5 17 328 5
5 14 344 5
5 16 344 5
4.5 14 338 5
4.5 17 338 4
4 14 322 4
4 14 333 4
4 16 333 4
4 17 341 3
4 16 341 5

Hugo Kornelis

3/21/2007 11:21:00 PM

On 21 Mar 2007 13:54:54 -0700, Tyrenta wrote:

>I'm trying to get the final column to simply sort by, and list the
>average review for that item, but still see the detail
(snip)
>
>And i'd like to have the output in the exact same format (item, rater,
>rating),but sorted by average rating per item:
>
>AvgOfrating reviewer_id item_id rating
>8 14 327 7
>8 16 327 9
(snip)

Hi Tyrenta,

Try:

SELECT (SELECT AVG(b.Rating)
FROM YourTable AS b
WHERE b.reviewer_id = a.reviewer_id
AND b.item_id = a.item_id) AS AvgOfRating,
a.reviewer_id, a.item_id, a.rating
FROM YourTable AS a;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Tyrenta

3/22/2007 1:02:00 PM

On Mar 21, 7:20 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On 21 Mar 2007 13:54:54 -0700, Tyrenta wrote:
>
> >I'm trying to get the final column to simply sort by, and list the
> >average review for that item, but still see the detail
> (snip)
>
> >And i'd like to have the output in the exact same format (item, rater,
> >rating),but sorted by average rating per item:
>
> >AvgOfrating reviewer_id item_id rating
> >8 14 327 7
> >8 16 327 9
>
> (snip)
>
> Hi Tyrenta,
>
> Try:
>
> SELECT (SELECT AVG(b.Rating)
> FROM YourTable AS b
> WHERE b.reviewer_id = a.reviewer_id
> AND b.item_id = a.item_id) AS AvgOfRating,
> a.reviewer_id, a.item_id, a.rating
> FROM YourTable AS a;
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hug...

Thanks all very much that works great -- one last if I may I can't get
it to sort by AvgOfRating (at least in Access):

ORDER BY AvgOfRating DESC;

gives me an error, as does

ORDER BY a.AvgOfRating DESC;

Tyrenta

3/22/2007 1:18:00 PM

Hugo Kornelis

3/23/2007 11:25:00 PM

On 22 Mar 2007 06:02:06 -0700, Tyrenta wrote:

(snip)
>> SELECT (SELECT AVG(b.Rating)
>> FROM YourTable AS b
>> WHERE b.reviewer_id = a.reviewer_id
>> AND b.item_id = a.item_id) AS AvgOfRating,
>> a.reviewer_id, a.item_id, a.rating
>> FROM YourTable AS a;
>>
>> --
>> Hugo Kornelis, SQL Server MVP
>> My SQL Server blog:http://sqlblog.com/blogs/hug...
>
>Thanks all very much that works great -- one last if I may I can't get
>it to sort by AvgOfRating (at least in Access):
>
>ORDER BY AvgOfRating DESC;
>
>gives me an error, as does
>
>ORDER BY a.AvgOfRating DESC;

Hi Tyrenta,

Hmmm. This is ANSI standard SQL, so if Access doesn't like it, it has to
be one of the many deviations from the standard that Access has.

I don't know much about Access, so maybe you'll have to ask this in an
Access group - but before you do that, you might try if this works:

SELECT (SELECT AVG(b.Rating)
FROM YourTable AS b
WHERE b.reviewer_id = a.reviewer_id
AND b.item_id = a.item_id) AS AvgOfRating,
a.reviewer_id, a.item_id, a.rating
FROM YourTable AS a
ORDER BY 1;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

microsoft.public.sqlserver.programming

Simple Newbie Question (I hope) -- Subquery using 1 table

Tyrenta

masri999

Hugo Kornelis

Tyrenta

Hugo Kornelis

Tyrenta

Tyrenta

Hugo Kornelis

x Login to ForumsZone