DevX Home Today's Headlines   Articles Archive   Tip Bank   Forums

# Thread: SQL Help

1. Registered User
Join Date
Mar 2008
Posts
11

## SQL Help

Hello,
I need to make a query where I can sum up values in a row. I am wanting to setup it up using SQL.

My table is setup as such

__1 2 3 4 5 6
1 A B C A D C
2 A C D A B C
3 A B C A B C
4 A B F A C C
5 A B C A B C
6 A B C A B C

What I need it to do is sum up the row (not columns) and at the same time set values such as (A=4, B=3, C=2, D=1, F=0).
Any ideas on how to do this. Thanks

2. Registered User
Join Date
Feb 2008
Posts
161
Originally Posted by carbon_13
Hello,
I need to make a query where I can sum up values in a row. I am wanting to setup it up using SQL.

My table is setup as such

__1 2 3 4 5 6
1 A B C A D C
2 A C D A B C
3 A B C A B C
4 A B F A C C
5 A B C A B C
6 A B C A B C

What I need it to do is sum up the row (not columns) and at the same time set values such as (A=4, B=3, C=2, D=1, F=0).
Any ideas on how to do this. Thanks

So taking row 1 as an example, you would want to change the letters to values and then add them, like:

4 + 3 + 2 + 4 + 1 + 2

= 16

Will the A's always be equal to 4 and B's always be equal to 3, etc. or is it different for every row?

Also, are all the possible values on each row known? ie - Only A - F or could there be others?

3. Registered User
Join Date
Mar 2008
Posts
11

## SQL Help

Yes, row 1 would add as such:
4 + 3 + 2 + 4 + 1 + 2

= 16

Not all A's will (B's, C's,....) will have the same value. Column 2 might have a different value for A than column 1. This would only be for letter A, B, C, D, F. Only these letter since it is being used for gpa calculation from grades.
Thanks
carbon_13

4. Registered User
Join Date
Feb 2008
Posts
161
What would be the determining factor for what each letter equals in each column?

EDIT:
Will the letters in each column always equal the same value in that column?
Last edited by Slope; 03-13-2008 at 03:49 PM.

5. Registered User
Join Date
Mar 2008
Posts
11

## SQL Help

Once the values were assigned for each column they wouldn't change.For example say the columns two will alsways have (A=12, B=9, C=6, D=3, F=0).
carbon_13

6. Registered User
Join Date
Mar 2008
Posts
11

## SQL Help

So column 2 will have the values (A=12, B=9, C=6, D=3, F=0). while the other columns have values of (A=4, B=3, C=2, D=1, F=0). So for row 1 the sum would have to be
A B C A D C
4 + 9 + 2 + 4 + 1 + 2
=22

So I want be able to make a query using this setup from data in a table. Thanks

carbon_13

7. Registered User
Join Date
Feb 2008
Posts
161
Okay. There are a couple of ways to do this. First, you could use a big, nasty case statement on each column and hard code the values for each grade there. Or a little cleaner way would be to create a table to resolve the grade letters for each column to the value that you want for that column. In this example I called it, "GradeValues"

Create the GradeValues table with the following columns and insert data for each column's grades -

A, 4, 1
B, 3, 1
C, 2, 1
D, 1, 1
F, 0, 1

A, 12, 2
B, 9, 2
C, 6, 2
D, 3, 2
F, 0, 2
...
...
A, 4, 6
B, 3, 6
C, 2, 6
D, 1, 6
F, 0, 6

Then join that table to every column and add the GradeValues together -
(Assumes your table with the grades in it is called "Grades")

Code:
```SELECT

JOIN GradeValues gv1 ON gv1.GradeLetter = g.Col1 AND gv1.ColNum = 1
JOIN GradeValues gv2 ON gv2.GradeLetter = g.Col2 AND gv2.ColNum = 2
JOIN GradeValues gv3 ON gv3.GradeLetter = g.Col3 AND gv3.ColNum = 3
JOIN GradeValues gv4 ON gv4.GradeLetter = g.Col4 AND gv4.ColNum = 4
JOIN GradeValues gv5 ON gv5.GradeLetter = g.Col5 AND gv5.ColNum = 5
JOIN GradeValues gv6 ON gv6.GradeLetter = g.Col6 AND gv6.ColNum = 6```
Last edited by Slope; 03-14-2008 at 02:38 AM.

8. Registered User
Join Date
Mar 2008
Posts
11

## SQL Help

Thanks for the help. What do you mean by
'Then join that table to every column and add the GradeValues together '

Well I tried the code buy it said that there was a missing operator in the expression. I am new to SQL, so bear with me. Thanks for the help, I appreciate it.
carbon_13

9. Registered User
Join Date
Feb 2008
Posts
161
EDIT:
Post removed to avoid confusion.
Last edited by Slope; 03-14-2008 at 04:13 PM.

10. Registered User
Join Date
Feb 2008
Posts
161
Also, if you just tried to run the above query as is, chances are that is why your are getting an error. I did not know the exact table name and column names for your table, so I called the table Grades and the columns Col1..Col6. You will need to edit the query and change those to match your table.

11. Registered User
Join Date
Mar 2008
Posts
11

## SQL Help

Thanks for the help once again. I was not aware that the columns werer called Col1 Col2.....So I changed them and still got the syntax error. Being trying to figure it out but no luck. So tried the Code for CREATE TABLE, but it also gave me a syntax error, and after the syntax error it highlights ON that proceeds the [PRIMARY]. I don't know what it means when it highlights just the ON.

GradeLetter VARCHAR(1) NOT NULL,
GradeValue INT NOT NULL,
ColNum INT NOT NULL
) ON [PRIMARY]
GO'

Maybe it is fault on my end, like I said I am not familiar with SQL. Thanks for the emphasis to help.
carbon_13

12. Registered User
Join Date
Feb 2008
Posts
161
You actually don't need the "ON [PRIMARY]" part. Just remove it and run the statement without it. Then execute all of the inserts to put the data into the table. Once you have done that, if you still have trouble getting the query right, maybe you can post the name of your table and the column names and I will help you fix it.

13. Registered User
Join Date
Mar 2008
Posts
11
I erased the "ON [PRIMARY]" part but still no luck. Ok I will give the table names. The to table names are "StudentGrades" and "GradeValues". In the "StudentsGrades" the columns are
"StuName", "A1", "A2", "A3", "A4", "A5", "A6"
So under the "A1"....."A6" the letter grades would go under. For the table "GradeValues" the columns are:
"Grade", "Points" and "ColumnNumber"
(This part is from what you suggested).
carbon_13

14. Registered User
Join Date
Mar 2008
Posts
11
Would you be able to post a working database that you are suggesting, it might be just on my end. Thanks
carbon_13

15. Registered User
Join Date
Feb 2008
Posts
161
Okay, but first I suppose we should backup a minute and do this right. The fact that you are working with the data in crosstabbed format causes a couple of problems. First, it makes queries more complex. Second, (and most importantly), the code breaks if you have to add another column (ie- another test).

What you have now is one record for each student with multiple tests on it (A1..A6). A better way would be to have a separate record for each test for every student. So instead of the StudentGrades table looking like:

StuName, A1, A2, A3, A4, A5, A6
Mikey, A, B, C, A, D, C
...

It would look like:

Mikey, A, 1
Mikey, B, 2
Mikey, C, 3
Mikey, A, 4
Mikey, D, 5
Mikey, C, 6

Now when you need to add another test, you just add another record:
Mikey, A, 7

With the data in this form, the query is also much simpler because you can use the built-in SUM and AVG aggregate functions.

Code:
```SELECT
sg.StuID,
sg.StuName,
SUM(gv.Points) TotalPoints,
AVG(gv.Points * 1.0) GPA
JOIN GradeValues gv ON
gv.TestNum = sg.TestNum
GROUP BY sg.StuID, sg.StuName
ORDER BY sg.StuID```

I don't think sending you the database will work because you will probably not be able to attach it if the paths to the .mdf and log files are different.

But here is a script to create both of the new tables and insert the test data. (You should either rename your old tables or put these in a separate database.)

If you have trouble getting the create table statements to run, you can just create the tables manually. Then run the inserts to populate them.

One other thing is that I added a StuID column. The StuName column should really be in another table, along with other info about the student.

Code:
```CREATE TABLE dbo.StudentGrades(
StuID int NOT NULL,
StuName varchar(50) NOT NULL,
Grade varchar(1) NOT NULL,
TestNum int NOT NULL
)
GO

TestNum int NOT NULL,
Grade varchar(1) NOT NULL,
Points int NOT NULL
)
GO

INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1001, 'Mikey', 'A', 1)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1001, 'Mikey', 'B', 2)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1001, 'Mikey', 'C', 3)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1001, 'Mikey', 'A', 4)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1001, 'Mikey', 'D', 5)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1001, 'Mikey', 'C', 6)

INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1002, 'Sally', 'A', 1)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1002, 'Sally', 'C', 2)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1002, 'Sally', 'D', 3)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1002, 'Sally', 'A', 4)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1002, 'Sally', 'B', 5)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1002, 'Sally', 'C', 6)

INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1003, 'Bailey', 'A', 1)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1003, 'Bailey', 'B', 2)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1003, 'Bailey', 'C', 3)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1003, 'Bailey', 'A', 4)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1003, 'Bailey', 'B', 5)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1003, 'Bailey', 'C', 6)

INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1004, 'Steven', 'A', 1)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1004, 'Steven', 'B', 2)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1004, 'Steven', 'F', 3)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1004, 'Steven', 'A', 4)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1004, 'Steven', 'C', 5)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1004, 'Steven', 'C', 6)

INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1005, 'Tommy', 'A', 1)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1005, 'Tommy', 'B', 2)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1005, 'Tommy', 'C', 3)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1005, 'Tommy', 'A', 4)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1005, 'Tommy', 'B', 5)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1005, 'Tommy', 'C', 6)

INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1006, 'Billy', 'A', 1)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1006, 'Billy', 'B', 2)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1006, 'Billy', 'C', 3)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1006, 'Billy', 'A', 4)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1006, 'Billy', 'B', 5)
INSERT INTO StudentGrades(StuID, StuName, Grade, TestNum) VALUES (1006, 'Billy', 'C', 6)

INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (1, 'A',  4)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (1, 'B',  3)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (1, 'C',  2)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (1, 'D',  1)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (1, 'F',  0)

INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (2, 'A', 12)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (2, 'B',  9)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (2, 'C',  6)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (2, 'D',  3)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (2, 'F',  0)

INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (3, 'A',  4)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (3, 'B',  3)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (3, 'C',  2)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (3, 'D',  1)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (3, 'F',  0)

INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (4, 'A',  4)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (4, 'B',  3)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (4, 'C',  2)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (4, 'D',  1)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (4, 'F',  0)

INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (5, 'A',  4)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (5, 'B',  3)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (5, 'C',  2)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (5, 'D',  1)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (5, 'F',  0)

INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (6, 'A',  4)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (6, 'B',  3)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (6, 'C',  2)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (6, 'D',  1)
INSERT INTO GradeValues(TestNum, Grade, Points) VALUES (6, 'F',  0)```
Last edited by Slope; 03-14-2008 at 04:07 PM.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

 FAQ Latest Articles Java .NET XML Database Enterprise
 Questions? Contact us. C++ Web Development Wireless Latest Tips Open Source