Jump to content


Photo

help writing sql statement using AVG and a JOIN


  • Please log in to reply
2 replies to this topic

#1 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 06 January 2006 - 04:10 PM

How do I write a sql statement that will join the tables and get the average of each illustrator's score?

I'm trying to create a rating system where users can rate multiple illustrators on one page and it shows the illustrator's existing score
[img src=\"http://www.n8w.com/temp/rating.jpg\" border=\"0\" alt=\"IPB Image\" /]

I need help writing a sql statement in order to get the score of each illustrator.

I have two tables

[img src=\"http://www.n8w.com/temp/votesql.gif\" border=\"0\" alt=\"IPB Image\" /]

How do I write a sql statement that will join the tables and get the average of each illustrator's score?

thanks!!!!!!

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 06 January 2006 - 05:07 PM

[!--quoteo(post=334030:date=Jan 6 2006, 11:10 AM:name=n8w)--][div class=\'quotetop\']QUOTE(n8w @ Jan 6 2006, 11:10 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
How do I write a sql statement that will join the tables and get the average of each illustrator's score?

I'm trying to create a rating system where users can rate multiple illustrators on one page and it shows the illustrator's existing score
[img src=\"http://www.n8w.com/temp/rating.jpg\" border=\"0\" alt=\"IPB Image\" /]

I need help writing a sql statement in order to get the score of each illustrator.

I have two tables

[img src=\"http://www.n8w.com/temp/votesql.gif\" border=\"0\" alt=\"IPB Image\" /]

How do I write a sql statement that will join the tables and get the average of each illustrator's score?

thanks!!!!!!
[/quote]

try this:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] a.illustrator_id, AVG(score) AS avg FROM illustrator_table a RIGHT JOIN score_table b ON a.illustrator_id = b.illustrator_id GROUP BY a.illustrator_id;
[!--sql2--][/div][!--sql3--]
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 06 January 2006 - 08:13 PM

[!--quoteo(post=334045:date=Jan 6 2006, 02:07 PM:name=obsidian)--][div class=\'quotetop\']QUOTE(obsidian @ Jan 6 2006, 02:07 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
try this:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] a.illustrator_id, AVG(score) AS avg FROM illustrator_table a RIGHT JOIN score_table b ON a.illustrator_id = b.illustrator_id GROUP BY a.illustrator_id; [!--sql2--][/div][!--sql3--]
[/quote]

THANKS SOOOOOOOOOOOOOOOOOO MUCH!!!!!! This is awesome
..sorry about the double posts .. I wasn't sure if this forum got much traffic so I posted it in the general .. thanks




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users