Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2002
    Location
    San Juan, Puerto Rico
    Posts
    29

    Red face Unanswered: Sumarized in SQL

    I got a simple question. Can I calculate different columns in a table that have numbers in currency?
    My table looks something like this (debit1, debit2,debit3,debit4,debit5,debit6 and the same for credit1 true credit6) and the fields for totals are tdebit and tcredit. If this posible please help and if not tell me what I got to do.

    Thanks

  2. #2
    Radzi Guest

    Re: Sumarized in SQL

    You can add the columns together, (debit1+debit2+debit3) etc, but seriously,
    I think that you should redesign the table structure.

    radzi
    "luisantonio" wrote in message
    news:2430164.1043360091@dbforums.com...
      > I got a simple question. Can I calculate different columns in a table
      > that have numbers in currency?
      > My table looks something like this (debit1,
      > debit2,debit3,debit4,debit5,debit6 and the same for credit1 true
      > credit6) and the fields for totals are tdebit and tcredit. If this
      > posible please help and if not tell me what I got to do.
      > Thanks
      > --
      > Posted via http://dbforums.com

  3. #3
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Lightbulb Re: Sumarized in SQL

    Originally posted by luisantonio
    I got a simple question. Can I calculate different columns in a table that have numbers in currency?
    My table looks something like this (debit1, debit2,debit3,debit4,debit5,debit6 and the same for credit1 true credit6) and the fields for totals are tdebit and tcredit. If this posible please help and if not tell me what I got to do.

    Thanks
    Hello,

    do you you mean you want to summarize the different columns ?
    Use: Select SUM(debit1), SUM(debit2) from table where ...
    If you want to update the tdebit field use:

    UPDATE table SET tdebit = SUM(debit1)+SUM(debit2) ....

    Hope that helps ?

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

  4. #4
    Join Date
    Sep 2002
    Location
    San Juan, Puerto Rico
    Posts
    29

    Thanks

    I'll try that.

  5. #5
    Join Date
    Sep 2002
    Location
    San Juan, Puerto Rico
    Posts
    29

    Re: Sumarized in SQL

    Originally posted by alligatorsql.com
    Hello,

    do you you mean you want to summarize the different columns ?
    Use: Select SUM(debit1), SUM(debit2) from table where ...
    If you want to update the tdebit field use:

    UPDATE table SET tdebit = SUM(debit1)+SUM(debit2) ....

    Hope that helps ?

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com
    The sum work, but I could not make the totals for tdebit.
    This is what I write
    SELECT
    SUM(debit1),SUM(debit2),SUM(debit3),SUM(debit4),SU M(debit5),SUM(debit6)
    FORM TABLE

    UPDATE table SET tdebit = SUM(debit1)+SUM(debit2)+SUM(debit3)+SUM(debit4)+SU M(debit5)+SUM(debit6)

    What did I miss?

    Thanks for all your Help.

  6. #6
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: Sumarized in SQL

    I suppose the statement should be:

    UPDATE table SET tdebit = debit1+debit2+....+debit6

    In this case tdebit is updated in the whole table. If you want to update a specific record you'll want to do something like

    UPDATE table SET tdebit = debit1+debit2+....+debit6
    WHERE <primary key>=<some value>

    BTW, I agree with Radzi that you might want to restructure your tables at some point in time. What do you do when you need debit7?!

    Ad Dieleman.

  7. #7
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Re: Sumarized in SQL

    Originally posted by luisantonio
    The sum work, but I could not make the totals for tdebit.
    This is what I write
    SELECT
    SUM(debit1),SUM(debit2),SUM(debit3),SUM(debit4),SU M(debit5),SUM(debit6)
    FORM TABLE

    UPDATE table SET tdebit = SUM(debit1)+SUM(debit2)+SUM(debit3)+SUM(debit4)+SU M(debit5)+SUM(debit6)

    What did I miss?

    Thanks for all your Help.
    Hello,

    all the others are right. Thing about a new design and reorganise your table - but, this would answer your question ?!

    Which error do you get and what do you want to save exactly in tdebit ?
    Is the SUM really a value, that you want to save ? Or is it better to calculate it every tim you need the value ?
    If you save the SUM of all debit field in tdebit you have a lot of redundat datas ? Do you know, what I mean ?

    Best regards

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

  8. #8
    Join Date
    Sep 2002
    Location
    San Juan, Puerto Rico
    Posts
    29

    SQL SUM

    Im working with only 6 fields for debit and credit. In tdebit I want the the total sum of debit1...debit6 the exact calculation of those fields, this is like a receipt application for enter the payments of the suppliers. The user will enter this information true a web page.
    I want to make all the calculation work so then I can redesign my tables. This is where I am a little stuck.
    I try the (UPDATE table SET tdebit = debit1+debit2+....+debit6) to get my totals for debit but I keep getting a (syntax error in from clause).
    I am new to sql, I just know a little so if you guys can guide whith your help and knowledge I will be very happy and thankfull to all of you.

    Please help!!!


    Luis Antonio

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL SUM

    Originally posted by luisantonio
    Im working with only 6 fields for debit and credit. In tdebit I want the the total sum of debit1...debit6 the exact calculation of those fields, this is like a receipt application for enter the payments of the suppliers. The user will enter this information true a web page.
    I want to make all the calculation work so then I can redesign my tables. This is where I am a little stuck.
    I try the (UPDATE table SET tdebit = debit1+debit2+....+debit6) to get my totals for debit but I keep getting a (syntax error in from clause).
    I am new to sql, I just know a little so if you guys can guide whith your help and knowledge I will be very happy and thankfull to all of you.

    Please help!!!


    Luis Antonio
    Please state exactly what SQL statement you are tryimg, and what error message you get. The example you just gave is perferctly valid, and should work:

    UPDATE table SET tdebit = debit1+debit2+....+debit6;

  10. #10
    Join Date
    Sep 2002
    Location
    San Juan, Puerto Rico
    Posts
    29

    Error in SQL

    SELECT SUM (debit1+debit2+debit3+debit4+debit5+debit6)
    FROM suplidores
    UPDATE suplidores SET tdebito=debit1+debit2+debit3+debit4+debit5+debit6

    This is what I get, the error is:
    [Microsoft][ODBC Microsoft Access Driver]Syntax error in FROM Clause.

    I am working this with Dreamweaver MX.
    I need that when the users enters a value for debit or credit it automatically change my totals for tdebit and tcredit in the page and that total to be stored in my DB, can this be possible? I am working in my DB, but first I will like to make this to run so then I can concentrate more in the redesign of the DB.

  11. #11
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    No Select and Update in on statement

    Hello,

    you can not use the prompted statement in one command.
    Just use


    UPDATE suplidores SET tdebito=debit1+debit2+debit3+debit4+debit5+debit6


    Then you get a SUM of every record in your tdebito field.

    IF you need SUM of all tdebito fields then use
    SELECT SUM(tdebito) from suplidores

    Hope that helps ?

    Best regards
    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

Posting Permissions

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