SQL Query grouping subtotal quandry 1

[charanch]

Hello,

I've spent way too long trying to figure out how to group products with their categories and THEN show a subtotal at the end and show it all in a report.

I have a list of products sold in a date range. They need to be grouped by category, grouped by product and show the total items sold for each product and then show a subtotal at the end of each category. There are three tables: orders, products and items. Here's my query and it's working except I don't know how to do the subtotal thing. I know it's involved, but any ideas or changes to my design would be highly appreciated!

SELECT SUM(oitems.numitems * oitems.unitprice) AS ordertotal,
orders.odate, products.catalogid, products.cname,
SUM(oitems.numitems) AS totalitems, orders.orderid,
products.ccategory, categories.catextra,
categories.catdescription,
oitems.numitems * products.cost AS cost
FROM dbo.orders INNER JOIN
dbo.oitems ON
dbo.orders.orderid = dbo.oitems.orderid INNER JOIN
dbo.products ON
dbo.oitems.catalogid = dbo.products.catalogid INNER JOIN
dbo.categories ON
dbo.products.ccategory = dbo.categories.categoryid
WHERE (dbo.orders.oprocessed = 1)
GROUP BY dbo.orders.odate, dbo.products.catalogid,
dbo.products.cname, dbo.orders.orderid,
dbo.products.ccategory, dbo.categories.catextra,
dbo.categories.catdescription,
dbo.oitems.numitems * dbo.products.cost


Thanks very much!

 

[Bastien]

How are you creating the report? Via an application or some reporting tool?

What is the DB (functionality is higher with something like Oracle vs Access)?

With an app, you can create a local variable that acts as a counter for the total amount when the data is looped thru.

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[charanch]

Hi Bastien,

Thanks for your reply. SQL2000 database, no reporting tools, just straight code in an ASP. Thanks for your interest!

 

[Bastien]

The easiest way is to use a variable that acts like a counter in the asp program. When running thru the loop of records, just keep adding to the category counter till the category changes

oldCat=""    'stores the last run thru of the loops category
theCat=""    'stores the result from the db query
catTotal=0   'counter

do while not myRS.EOF

   'assign values to vars -->done for clarity
   theCat=myRS("categoryID")
   theProduct=myRS("ProductName")
   totalSale=myRS("cost")

   if oldCat<>theCat then
      response.write &quot;Total for category: &quot; & catTotal
      catTotal=totalSale     'reset counter to new amount
      oldCat=theCat          'change the category marker
   else
      catTotal=catTotal + totalSale
   end if

   'display the data however you do it

loop

'show the results from the final category
response.write &quot;Total for category: &quot; & catTotal

something like the above should work.


Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[charanch]

Thank you so much. Is there any way to show the category total and the END of the data group? I had tried this with my other code as well, but when I put the logic at the end, it displayed the total on each line. Thanks again for your help!!

 

[Bastien]

Not sure exactly what it is you want here...the code places the subtotal for the category after each category.

Should you want to place those totals after all the data, you can create an array of totals then loop thru the array after each one...

you may also want to look at the cube and rollup functionality in T-SQL to see if that matches your needs

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[charanch]

Many thanks, Bastien. I will give it another look. I thought it was placing the subtotals prior to the category. Thanks again.

 

[charanch]

Bastien, it works perfectly. Many thanks again for your help!