Results 1 to 5 of 5

Thread: SQL Help

  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: SQL Help

    I have a transaction table of which has the fields.
    transactionID, StudentID,transactionType, transactionDate, Amount.
    transactionId is the primary key.
    this table has about 23,000 rows

    There is also a table tblstudent in which all the students are listed with primary key StudentID.

    Now I need to add an additional field "balance" to this table.
    and the balance field needs to be populated against each row so that we get a sum(Amount) for each transactionID.

    I have tried to do something like pseudocode

    UPDATE tbltransaction
    SET (balance= (SELECT SUM(balance) WHERE transactiondate< currentTransactionDate AND studentID= currentStudentID )
    WHERE transactionID is from 1 to 23000.

    I am using MySQL

    Please help me with the SQL. Thank you very much in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one, could you do a SHOW CREATE TABLE for the student table table please

    two, why do you have a WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    1.
    CREATE TABLE IF NOT EXISTS `tblstudent` (
    `ID` int(250) NOT NULL AUTO_INCREMENT,
    `admissionNumber` float NOT NULL,
    `studentName` varchar(250) NOT NULL,
    PRIMARY KEY (`ID`)
    )

    CREATE TABLE IF NOT EXISTS `tbltransaction` (
    `ID` bigint(255) NOT NULL AUTO_INCREMENT,
    `transactionTypeID` varchar(250) NOT NULL,
    `studentID` int(250) NOT NULL,
    `date` datetime NOT NULL,
    `amount` float NOT NULL,
    `balance` double NOT NULL,
    PRIMARY KEY (`ID`)
    )
    2.
    It is not needed
    Last edited by sajanjacobk; 05-12-10 at 07:00.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't understand what value you want the balance column to have
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2010
    Posts
    3
    This is some thing like a bank account.
    And the total balance for customer as a sum account in a particular studentID.

    But when the transactions increased the need to put a balance field was necessary. This helps, since from a latest single record, we get the current Balance of the studentID.

Posting Permissions

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