Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts

    How to show calculate accum. QTY, Subtotal and grand total in ASP??

    Dear,

    i have the data i my Order simple Table:
    OrderID ItemID UnitPrice QTY
    =========================
    0001 A1 1.3 1
    0001 A3 0.8 6
    0002 A1 1.2 8
    0003 A5 7.0 20
    0003 A2 3.3 7
    0003 A4 0.5 9


    How to show the result as: (I just want to show once Order ID but with calculated QTY.)

    Order ID No of Item Total QTY Total
    ==================================
    0001 2 7 (6+1) $ 6.10
    0002 1 8 (8) $ 9.60
    0003 2 36 (20+7+9) $ 40.60
    ---------------------------------------------
    Total: 5 51 $ 56.30
    ===================================

    TQ
    Last edited by NinjaTurtle; 08-11-2004 at 11:16 AM.
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    what db-format is it? to achieve this, you'll need to be able to do a group_concat (for the quantity values between the ()) and you'd need to be ableto do a sum(UnitPrice*QTY)

    this is not the sort of query you'd typically want to do at runttime. If you frequentky need this info, then you need to precompute these overviewtables

    for MySQL, your typical query would look like

    select OrderID, sum(QTY) as totalitems, GROUP_CONCAT (QTY SEPERATOR ' + ') as qtylist, sum(UnitPrice*QTY) as totalprice FROM Order GROUP BY OrderID ORDER BY OrderID ASC
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Dear raf,

    Opps, sorry about the confusing u, the "+" sign is just a seperate of the table field's name.
    I already changed the question. that is juat a normal table with MS SQL.

    How to generate them in Loop?
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i don't understan your reply. the + i added is for the 0001 2 7 (6+1) $ 6.10


    computing this while looping through the recordset isn't realy an option, because when your table gets bigger, the responsetimes will increase and your servers performance will drop.

    the way to do it in a loop is by storing the OrderID in a variable (order), and then on each iteration, compaire the one from your current record with the one in that variable (the value from the previous record). If the same, then add the price, quantity etc to a variable.
    if different, then print out the value of your price and quantity + the orderID from your recordset + set the value for order to the orderID from your current record (like rs.Fields('OrderID').Value)
    and the same for the price and quantity variables

    Don't forget to also print out the value of these values after the loop! else you'll loose the last order.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

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