08-11-2004, 03:42 AM
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
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
08-11-2004, 10:18 AM
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?
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.