Just getting started with queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Just getting started with queries

Author

Topic

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-24 : 16:40:08

Hi, I'm new to this forum and new to sql. I am just started my first project and have with me a "teach yourself SQL in 24hrs" and an account here with sqlteam.com I have allot to learn but I am raring to go.

My first question I hope someone can help me with, its probs very simple but humour me. I am using ms sql server express 2005 if that helps.

I have a table named "scores" this table includes the following columns:

Entrantcode
Score

I have another table named "entrants" which has the following columns:

Entrantcode
Name
Age
Gender

The "scores" table is used to record the scores of entrants. Each entrant has their own code "Entrantcode" which people us to specify who they are scoring. So this table is updated every time a score is received, for example

Entrantcode Score
001 4
005 3
003 5
002 3
001 5
002 2

I HAVE MANAGED TO SORT ALL THE ABOVE, SO THE TABLES ARE SET UP ETC. BUT I NEED HELP WITH THE FOLLOWING:

What I want to do now is work out the average score (Avescore) for each entrant and put the results in a new table called "entrantleague", along with more details of each entrant based on their unique “Entrantcode”, these extra details come from the table "entrants" So what I want the new table "entrantleague" to output, for example is:

Entrantcode Name Age Gender Avescore
001 Jim 19 M 4.5
002 Jane 19 F 2.5
003 Paul 20 M 5
005 Karl 18 M 3

What I guess I need to do is create a query that divides that total score of each "entantcode" by the number of times it was scored, for instance, entrant code 001 was scored twice, first with a score of 5 then with a score of 4, so the total score was 9, that makes the average score (Avescore) of entrantcode 001, 4.5 (which is 9/2).

This needs to be done for each code with the output inserted into the "entrantleague" table. I also want this table sorted by the column "avescore" in descending order.

Everytime the table "Scores" is updated, then the above query needs to run to update the "Entrantleague" table.

I am sorry the question is so long, I know what I want to do but there are so many functions in SQL that I am trying to learn I don't know which one is appropriate and how to construct it.

I really appreciate it if someone could help me on this one.

Kind regards

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 17:03:26

select a.*, avg(score )as score from entrants a,scores b
where a.Entrantcode = b.Entrantcode
group by a.Entrantcode,a.Name,a.Age,a.Gender

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-24 : 17:13:53

You'll get an error on that a.* part.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-24 : 17:22:19

quote:
Originally posted by tkizer

You'll get an error on that a.* part.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

How do I avoid the error then

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 17:31:14

No Error on both 2000 and 2005 based on his table definitions.
BUT, yeah a.* is bad idea.. I shouldn't do that. very bad practice but quick and dirty

select a.Entrantcode,a.Name,a.Age,a.Gender, avg(score )as score from entrants a, scores b
where a.Entrantcode = b.Entrantcode
group by a.Entrantcode,a.Name,a.Age,a.Gender

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-24 : 17:38:33

quote:
Originally posted by hanbingl

No Error on both 2000 and 2005 based on his table definitions.
BUT, yeah a.* is bad idea.. I shouldn't do that. very bad practice but quick and dirty

select a.Entrantcode,a.Name,a.Age,a.Gender, avg(score )as score from entrants a, scores b
where a.Entrantcode = b.Entrantcode
group by a.Entrantcode,a.Name,a.Age,a.Gender

Will quick and dirty work then or is there a better way?
Can I use this as a trigger to run when the "scores" table is updated. And can I output the resluts to a new table "entrantsleague". How would I tell it to do that?

Thanks for your help guys.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 17:44:33

Do not update the score table..
use AVG function everytime you want to see the result.
Make a VIEW instead, when new records inserted to scores table, your view will be updated:


create view entrantsleague
as 
select a.Entrantcode,a.Name,a.Age,a.Gender, avg(score )as avgscore from entrants a, scores b
where a.Entrantcode = b.Entrantcode
group by a.Entrantcode,a.Name,a.Age,a.Gender


------------------------------------

to view entransleague:

select Entrantcode,Name,Age,Gender, Avgscore from entrantsleague
order by Avgscore desc

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-24 : 17:51:33

quote:
Originally posted by hanbingl

Do not update the score table..
use AVG function everytime you want to see the result.
Make a VIEW instead, when new records inserted to Entrant table, your view will be updated:

create view entrantsleaque
as
select a.Entrantcode,a.Name,a.Age,a.Gender, avg(score )as score from entrants a, scores b
where a.Entrantcode = b.Entrantcode
group by a.Entrantcode,a.Name,a.Age,a.Gender

Thanks for your tip. However, the "scores" table has to be updated, there will be new scores coming in all the time (And that is where the scores are placed), so there are new average scores needing to be worked out all the time, which will effect the "entrantleague" table. If you know what I mean, I need to keep a recored of every score given to the "entrantcode" in the "scores" table.

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-24 : 17:54:06

quote:
Originally posted by hanbingl

Do not update the score table..
use AVG function everytime you want to see the result.
Make a VIEW instead, when new records inserted to scores table, your view will be updated:

create view entrantsleague
as
select a.Entrantcode,a.Name,a.Age,a.Gender, avg(score )as avgscore from entrants a, scores b
where a.Entrantcode = b.Entrantcode
group by a.Entrantcode,a.Name,a.Age,a.Gender

------------------------------------

to view entransleague:

select Entrantcode,Name,Age,Gender, Avgscore from entrantsleague
order by Avgscore desc

Sorry, I think I miss understood you, did you mean do not update the "entrantsleague" table?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 17:58:09

yup, sorry update the score table..do not trigger..

The view will be updated accordingly, which you don't have to do anything at all to ENTRANTSLEAGUE view.
Just select from ENTRANTSLEAGUE view instead.

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-24 : 18:04:07

quote:
Originally posted by hanbingl

yup, sorry update the score table..do not trigger..

The view will be updated accordingly, which you don't have to do anything at all to ENTRANTSLEAGUE view.
Just select from ENTRANTSLEAGUE view instead.

That's brill, thanks,

If you wanted to, could you select that view and sort some of the information in it to update another table?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 18:13:40

You can treat the view as table. so yeah you can select any column in the view and use it to update another table.
ie. You can join tables with views

Read about the view part if you want to know more.

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-10-24 : 18:14:58

quote:
Originally posted by hanbingl

You can treat the view as table. so yeah you can select any column in the view and use it to update another table.
ie. You can join tables with views

Read about the view part if you want to know more.

Thanks for your time and help man.

All the best

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources