SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict AshleyH's Avatar
    Join Date
    Mar 2005
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    adding up a column

    Language: Coldfusion

    I have the following code. I want to be able to add up all the 'invoice_vat' and show this on the screen - is this possible?


    <cfquery name="rsInvoices" datasource="DSN">
    SELECT invoice_no, invoice_date, invoice_company, invoice_status, invoice_notes, invoice_amount, invoice_amount * invoice_vatrate as invoice_vat
    FROM tbl_sales
    ORDER BY id DESC
    </cfquery>

    Many thanks

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you want a Coldfusion solution, you should ask in that forum. The solution there will be to loop over the rows, and add the invoice_vat from each row to a variable to get the sum.

    The SQL solution is to just ask the database for the sum.
    Code:
    SELECT SUM(invoice_amount * invoice_vatrate) FROM tbl_sales

  3. #3
    SitePoint Addict AshleyH's Avatar
    Join Date
    Mar 2005
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for coming back to me. Not sure if I made myself clear on this one - I just want to add up 'invoice_vat' from the current rows. Is this more of a coldfusion item?

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I think that's what I just answered, both in terms of doing it in the program and in terms of having the database do it?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    depends what you mean by "current rows"

    in coldfusion, when you run a query, the result set comes back from the database and is held in memory until you're done with it

    we could, for example, print the query results out, formatted nicely on the page, and then, underneath that, we also want to print a total

    so in this case, we want both detailed and total data

    we wouldn't ask the database to do both, so we simply "re-use" the query results which are still sitting there in memory

    so, in this sense, these are the "current rows"

    and in coldfusion they're real easy to manipulate, you just run a query of query

    something like

    <cfquery name="rsInvoicesTotals" dbtype="query">
    SELECT SUM(invoice_amount * invoice_vatrate)
    AS invoice_vat_total
    FROM rsInvoices
    </cfquery>


    helps?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict AshleyH's Avatar
    Join Date
    Mar 2005
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both for coming back to me.


Bookmarks

Posting Permissions

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