Classic ASP using Grouping / Sum through SQL loop?

General Web Dev
1

Hi,

I have an old application written in classic ASP.

At present it runs some SQL (Call it SQL1) and pulls out all the orders, I.E Order Number, Order Date etc …

Inside this loop is another SQL (Call it SQL2) which loops through all the orderlines where the table IDs match.
At the moment the only info being collected from this SQL2 is a qty stored in the table which is summed up, and a total is provided for each Order Number in SQL1

Each Order might have the following info as its OrderLines

Product A
qty = 52

Product B
qty = 73

Product C
qty = 18

What I want to do is sum all this info together from all the open orders.
i.e. I want to print out
Product A - Total = 192 (total from all open orders)
Product B - Total = 280 (total from all open orders)
Product C - Total = 96 (total from all open orders)

I can use SQL2 in the loop because the Product Name and the Qty required are in this SQL2, but I’m just unsure where to structure my loops in order to group these fields grouped together whilst looping through all the orders …

Appreciate your help :slight_smile:
David

2

You could probably do it purely in the SQL statement with something like this:

select o_l.product_name, count(*) as product_count
    from order_lines as o_l
    join orders as o on o_l.order_id = o.id
    where o.status = 'open'
    group by o_l.product_name

This would get you a list of the totals for each product. Is that all you’re trying to accomplish?

3

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.