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.
| Author |
Topic |
|
lkrb9865
Starting Member
3 Posts |
 Posted - 2008-03-08 : 17:49:57
|
| I have one database named StudInfo. It has two tables named StudentInfo, and GradeInfo. StudentInfo conntains 4 columns. The 1st one is StudentID (PK) int, LastName varchar(10), FirstName varchar(10), and PhoneNumber int.GradeInfo contains 4 columns also StudentID (FK) int, GradeID varchar(10), Grade int, Date Datetime.What I would like to know is how using a T-sql query I could make a temp table with studentID, LastName, FirstName, and then the average of all the different types under GradeID. As of right now I have been limiting the names that are put into GradeID to Homework, Daily, Test, Quiz, and Bonus. When I say average I mean the average of all Homeworks under one studentID, and all Daily under one studentID... etc. I would like the info returned for each student in studentID. Allow Nulls has been turned off.Never assume someone knows what you are talking about. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-08 : 18:18:33
|
quote:
Originally posted by lkrb9865
Never assume someone knows what you are talking about.
Exactly.What?Select s.StudentID,g.GradeID,g.Avg(Grade) as AvgGradeINTO #temptable --<====this would create a temp tableFROM StudentInfo s inner join GradeInfo g on s.StudentID = g.StudentIDGroup by s.StudentID,g.GradeIDOrder by s.StudentID, g.GradeID you are talking about going row by row? not why SQL works the way it does..no need for temp tables and recycling. Just join, select, avg, and group byedited: added the INTO #temptable, just in case...
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
lkrb9865
Starting Member
3 Posts |
Posted - 2008-03-08 : 19:18:06
|
| Thanks for the tip about joining. I did not realize I could do that. My experience with SQL is about 3 days worth. Before that I had 0 days of databases experience.Never assume someone knows what you are talking about. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-08 : 19:23:02
|
quote:
Originally posted by lkrb9865
Thanks for the tip about joining. I did not realize I could do that. My experience with SQL is about 3 days worth. Before that I had 0 days of databases experience.Never assume someone knows what you are talking about.
Not a problem. I wasnt exactly sure if you knew what you were asking either..so I went with the join and the into #temp table line...Welcome to SQL.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-08 : 20:20:44
|
| Why store phone number as int? |
|
|
|
lkrb9865
Starting Member
3 Posts |
Posted - 2008-03-08 : 20:49:19
|
| I just stored the phone number as an int because at the moment it seemed like a good idea. If there is a better way to store the number please let me know.Never assume someone knows what you are talking about. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-08 : 20:50:58
|
Should store it as text.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
|
|
How to make a temp table using info fromtwo tables