SQL Help

[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

[Slope]

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?

[carbon_13]

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

[Slope]

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?

[carbon_13]

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

[carbon_13]

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

[Slope]

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 -

GradeLetter, GradeValue, ColNum
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")

SELECT 
    gv1.GradeValue Col1Val,
    gv2.GradeValue Col2Val,
    gv3.GradeValue Col3Val,
    gv4.GradeValue Col4Val,
    gv5.GradeValue Col5Val,
    gv6.GradeValue Col6Val,

    gv1.GradeValue + 
    gv2.GradeValue +
    gv3.GradeValue + 
    gv4.GradeValue +
    gv5.GradeValue + 
    gv6.GradeValue RowTotal

FROM Grades g
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

[carbon_13]

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

[Slope]

EDIT:
Post removed to avoid confusion.

[Slope]

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.

[carbon_13]

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.

'CREATE TABLE dbo.GradeValues(
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

[Slope]

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.

[carbon_13]

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

[carbon_13]

Would you be able to post a working database that you are suggesting, it might be just on my end. Thanks
carbon_13

[Slope]

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:

StuName, Grade, TestNum
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.

SELECT 
    sg.StuID,
    sg.StuName, 
    SUM(gv.Points) TotalPoints,
    AVG(gv.Points * 1.0) GPA
FROM StudentGrades sg
JOIN GradeValues gv ON 
    gv.TestNum = sg.TestNum
AND gv.Grade = sg.Grade
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.

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


CREATE TABLE dbo.GradeValues(
	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)