View Full Version : How to get the group result?

08-11-2004, 01:42 PM
[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

08-11-2004, 01:51 PM
select CustID, sum(QTY) as TotalQTY from table group by CustID

08-11-2004, 04:44 PM

how about if only can use ASP and not SQL to do it? Like Do..While, For loops or..... ??

08-11-2004, 06:38 PM
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