SQL Help


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 15 of 15

Thread: SQL Help

  1. #1
    Join Date
    Mar 2008
    Posts
    11

    Question 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. #2
    Join Date
    Feb 2008
    Posts
    162
    Quote 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?
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  3. #3
    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. #4
    Join Date
    Feb 2008
    Posts
    162
    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 04:49 PM.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  5. #5
    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. #6
    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. #7
    Join Date
    Feb 2008
    Posts
    162
    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")

    Code:
    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
    Last edited by Slope; 03-14-2008 at 03:38 AM.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  8. #8
    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. #9
    Join Date
    Feb 2008
    Posts
    162
    EDIT:
    Post removed to avoid confusion.
    Last edited by Slope; 03-14-2008 at 05:13 PM.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  10. #10
    Join Date
    Feb 2008
    Posts
    162
    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.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  11. #11
    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.

    '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

  12. #12
    Join Date
    Feb 2008
    Posts
    162
    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.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  13. #13
    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. #14
    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. #15
    Join Date
    Feb 2008
    Posts
    162
    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.

    Code:
    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.


    Code:
    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)
    Last edited by Slope; 03-14-2008 at 05:07 PM.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

Similar Threads

  1. Access to SQL server
    By Nate in forum Database
    Replies: 29
    Last Post: 05-09-2001, 11:04 AM
  2. Deadlock error.. how to remove
    By Chandra in forum VB Classic
    Replies: 0
    Last Post: 06-22-2000, 01:52 PM
  3. Please help me -- urgent -- deadlock error
    By chandra in forum VB Classic
    Replies: 0
    Last Post: 06-22-2000, 08:36 AM
  4. Re: ODBC error
    By Devaraj in forum Enterprise
    Replies: 0
    Last Post: 05-11-2000, 01:48 PM
  5. Re: Referential Integrity- New to SQL Server
    By Aaron in forum VB Classic
    Replies: 0
    Last Post: 03-17-2000, 04:24 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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center