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 get the group result?

    [CustID] [OfficeID] [QTY]
    ======+=====+===
    0001 AB1 1
    0001 XY3 2
    0002 AB1 1
    0003 PC5 4
    0003 DD2 2
    0003 LO4 1


    how to get result like:
    [CustID] [Total QTY]
    ================
    0001 3
    0002 1
    0003 7

    Do WHile rs.eof
    .
    .
    . ' record should listed here
    .
    rs.movenext
    Loop
    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
    select CustID, sum(QTY) as TotalQTY from table group by CustID
    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,

    how about if only can use ASP and not SQL to do it? Like Do..While, For loops or..... ??
    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
    what is your problem ?

    all operation, like this sort of agregation, that can be done in sql, should be done in sql bequase databases are optimised for this sort of functions + the keep the recordsets much smaller which reduces responsetimes, reduces networktraffic, reduces serverside processing.

    there i no reason not to do it on the db.

    if you per se wanna do it in sp, the reas one of my post in another of your threads:
    check if the custID is the same as the one from the previous iteration (stored in a variable), and if so, increment your QTY variable. If no, print the QTY variable and assign the current records custID and QTY to your variables. When the loop is done, again print the value ogf the custID and the QTY variable
    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
    •