Group by with subtotals

I can't figure out how to get the subtotals into my table. This is a
group by SQL using a Do Loop to move through the records.

I would like my table to look like this:
Toner
Date Price Quantity Total
Date Price Quantity Total
Date Price Quantity Total
Subtotal <--how do I get this here
Toner
Date Price Quantity Total
Date Price Quantity Total
Date Price Quantity Total
Subtotal <--how do I get this here
Total of Subtotals

Here is my code:
<%
Set objConn = Server.CreateObject("ADODB.Connection")
ObjConn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA
SOURCE=c:\Toner.mdb"

mySQL = "SELECT TblToner.ID, TblToner.Date, TblToner.Price,
TblToner.Quantity, TblToner.Toner,
SUM(TblToner.Price*TblToner.Quantity) AS Total
FROM TblToner GROUP BY TblToner.Id, TblToner.Date, TblToner.Toner,
TblToner.Price ORDER BY TblToner.Toner"
Set objRS1 = Server.CreateObject("ADODB.Recordset")
objRS1.Open mySQL, objConn %>

Re: Group by with subtotals

rcjokibbe@netzero.com wrote:
> I can't figure out how to get the subtotals into my table. This is a
> group by SQL using a Do Loop to move through the records.
>
> I would like my table to look like this:
> Toner
> Date Price Quantity Total
> Date Price Quantity Total
> Date Price Quantity Total
> Subtotal <--how do I get this here
> Toner
> Date Price Quantity Total
> Date Price Quantity Total
> Date Price Quantity Total
> Subtotal <--how do I get this here
> Total of Subtotals
>

You should use a disconnected recordset so that you can minimize the time
you are connected to the database:

Set objRS1 = Server.CreateObject("ADODB.Recordset")
objRS1.CursorLocation=3 '3=adUseClient
objRS1.Open mySQL, objConn,,,1 '1=adCmdText
Set objRS1.ActiveConnection=Nothing
'You can now close the connection prior to processing the recordset

You have two choices:
1. Use a couple of variables (curID, newID) to keep track of the ID being
processed, using another variable to store the running sum of the totals for
that ID. When a new ID is encountered, write the subtotal to Response and
reset the subtotal variable to 0

2. Run a second query to retrieve the subtotals into a second disconnected
recordset:
mySQL = "SELECT TblToner.ID,
SUM(TblToner.Price*TblToner.Quantity) AS Total
FROM TblToner GROUP BY TblToner.Id"
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.CursorLocation=3 '3=adUseClient
objRS2.Open mySQL, objConn,,,1 '1=adCmdText
Set objRS2.ActiveConnection=Nothing
objConn.Close
In the loop through the first recordset, use a couple of variables (curID,
newID) to keep track of the ID being processed, and when a new ID is
encountered, use the objRS2.Filter="ID=" & curID
curSubtotal=objRS2("Total")

I would probably use method 1

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Group by with subtotals

Thanks for the reply. I've been trying method 1 but I can't grasp how
to write it into my code. I've got the running sum part (totals) I
don't know how to keep track of new and current id or where to place
those varialbles in my code to keep track when the id changes. Can
you, or anyone, give me an example? Thanks for the connection closing
tip too.


Bob Barrows [MVP] wrote:
> rcjokibbe@netzero.com wrote:
> > I can't figure out how to get the subtotals into my table. This is a
> > group by SQL using a Do Loop to move through the records.
> >
> > I would like my table to look like this:
> > Toner
> > Date Price Quantity Total
> > Date Price Quantity Total
> > Date Price Quantity Total
> > Subtotal <--how do I get this here
> > Toner
> > Date Price Quantity Total
> > Date Price Quantity Total
> > Date Price Quantity Total
> > Subtotal <--how do I get this here
> > Total of Subtotals
> >
>
> You should use a disconnected recordset so that you can minimize the time
> you are connected to the database:
>
> Set objRS1 = Server.CreateObject("ADODB.Recordset")
> objRS1.CursorLocation=3 '3=adUseClient
> objRS1.Open mySQL, objConn,,,1 '1=adCmdText
> Set objRS1.ActiveConnection=Nothing
> 'You can now close the connection prior to processing the recordset
>
> You have two choices:
> 1. Use a couple of variables (curID, newID) to keep track of the ID being
> processed, using another variable to store the running sum of the totals for
> that ID. When a new ID is encountered, write the subtotal to Response and
> reset the subtotal variable to 0
>
> 2. Run a second query to retrieve the subtotals into a second disconnected
> recordset:
> mySQL = "SELECT TblToner.ID,
> SUM(TblToner.Price*TblToner.Quantity) AS Total
> FROM TblToner GROUP BY TblToner.Id"
> Set objRS2 = Server.CreateObject("ADODB.Recordset")
> objRS2.CursorLocation=3 '3=adUseClient
> objRS2.Open mySQL, objConn,,,1 '1=adCmdText
> Set objRS2.ActiveConnection=Nothing
> objConn.Close
> In the loop through the first recordset, use a couple of variables (curID,
> newID) to keep track of the ID being processed, and when a new ID is
> encountered, use the objRS2.Filter="ID=" & curID
> curSubtotal=objRS2("Total")
>
> I would probably use method 1
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

Re: Group by with subtotals

It will be roughly like this:

dim newID, curID, subtotal

curID=objRS1("ID")
subtotal=0
Do until objRS1.eof
newID = objRS1("ID")
if newID <> curID then
'create the subtotal row here and write the value of subtotal to
it
curID = newID
subtotal = 0
end if
subtotal = subtotal + objRS1("Total")
....
loop

rcjokibbe@netzero.com wrote:
> Thanks for the reply. I've been trying method 1 but I can't grasp how
> to write it into my code. I've got the running sum part (totals) I
> don't know how to keep track of new and current id or where to place
> those varialbles in my code to keep track when the id changes. Can
> you, or anyone, give me an example? Thanks for the connection closing
> tip too.
>
>
> Bob Barrows [MVP] wrote:
>> rcjokibbe@netzero.com wrote:
>>> I can't figure out how to get the subtotals into my table. This is
>>> a group by SQL using a Do Loop to move through the records.
>>>
>>> I would like my table to look like this:
>>> Toner
>>> Date Price Quantity Total
>>> Date Price Quantity Total
>>> Date Price Quantity Total
>>> Subtotal <--how do I get this here
>>> Toner
>>> Date Price Quantity Total
>>> Date Price Quantity Total
>>> Date Price Quantity Total
>>> Subtotal <--how do I get this here
>>> Total of Subtotals
>>>
>>
>> You should use a disconnected recordset so that you can minimize the
>> time you are connected to the database:
>>
>> Set objRS1 = Server.CreateObject("ADODB.Recordset")
>> objRS1.CursorLocation=3 '3=adUseClient
>> objRS1.Open mySQL, objConn,,,1 '1=adCmdText
>> Set objRS1.ActiveConnection=Nothing
>> 'You can now close the connection prior to processing the recordset
>>
>> You have two choices:
>> 1. Use a couple of variables (curID, newID) to keep track of the ID
>> being processed, using another variable to store the running sum of
>> the totals for that ID. When a new ID is encountered, write the
>> subtotal to Response and reset the subtotal variable to 0
>>
>> 2. Run a second query to retrieve the subtotals into a second
>> disconnected recordset:
>> mySQL = "SELECT TblToner.ID,
>> SUM(TblToner.Price*TblToner.Quantity) AS Total
>> FROM TblToner GROUP BY TblToner.Id"
>> Set objRS2 = Server.CreateObject("ADODB.Recordset")
>> objRS2.CursorLocation=3 '3=adUseClient
>> objRS2.Open mySQL, objConn,,,1 '1=adCmdText
>> Set objRS2.ActiveConnection=Nothing
>> objConn.Close
>> In the loop through the first recordset, use a couple of variables
>> (curID, newID) to keep track of the ID being processed, and when a
>> new ID is encountered, use the objRS2.Filter="ID=" & curID
>> curSubtotal=objRS2("Total")
>>
>> I would probably use method 1
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Group by with subtotals

Thank you, that worked great. I had to add an additional If curId =
newId then write subtotal outside the loop as my last Id in the table
didn't show a subtotal. Thanks for you help, This has cut my original
code from 3616 lines to 111 because I used to have a objRS for each id.
Now onto showing total on hand in the for each id.


Bob Barrows [MVP] wrote:
> It will be roughly like this:
>
> dim newID, curID, subtotal
>
> curID=objRS1("ID")
> subtotal=0
> Do until objRS1.eof
> newID = objRS1("ID")
> if newID <> curID then
> 'create the subtotal row here and write the value of subtotal to
> it
> curID = newID
> subtotal = 0
> end if
> subtotal = subtotal + objRS1("Total")
> ....
> loop
>
> rcjokibbe@netzero.com wrote:
> > Thanks for the reply. I've been trying method 1 but I can't grasp how
> > to write it into my code. I've got the running sum part (totals) I
> > don't know how to keep track of new and current id or where to place
> > those varialbles in my code to keep track when the id changes. Can
> > you, or anyone, give me an example? Thanks for the connection closing
> > tip too.
> >
> >
> > Bob Barrows [MVP] wrote:
> >> rcjokibbe@netzero.com wrote:
> >>> I can't figure out how to get the subtotals into my table. This is
> >>> a group by SQL using a Do Loop to move through the records.
> >>>
> >>> I would like my table to look like this:
> >>> Toner
> >>> Date Price Quantity Total
> >>> Date Price Quantity Total
> >>> Date Price Quantity Total
> >>> Subtotal <--how do I get this here
> >>> Toner
> >>> Date Price Quantity Total
> >>> Date Price Quantity Total
> >>> Date Price Quantity Total
> >>> Subtotal <--how do I get this here
> >>> Total of Subtotals
> >>>
> >>
> >> You should use a disconnected recordset so that you can minimize the
> >> time you are connected to the database:
> >>
> >> Set objRS1 = Server.CreateObject("ADODB.Recordset")
> >> objRS1.CursorLocation=3 '3=adUseClient
> >> objRS1.Open mySQL, objConn,,,1 '1=adCmdText
> >> Set objRS1.ActiveConnection=Nothing
> >> 'You can now close the connection prior to processing the recordset
> >>
> >> You have two choices:
> >> 1. Use a couple of variables (curID, newID) to keep track of the ID
> >> being processed, using another variable to store the running sum of
> >> the totals for that ID. When a new ID is encountered, write the
> >> subtotal to Response and reset the subtotal variable to 0
> >>
> >> 2. Run a second query to retrieve the subtotals into a second
> >> disconnected recordset:
> >> mySQL = "SELECT TblToner.ID,
> >> SUM(TblToner.Price*TblToner.Quantity) AS Total
> >> FROM TblToner GROUP BY TblToner.Id"
> >> Set objRS2 = Server.CreateObject("ADODB.Recordset")
> >> objRS2.CursorLocation=3 '3=adUseClient
> >> objRS2.Open mySQL, objConn,,,1 '1=adCmdText
> >> Set objRS2.ActiveConnection=Nothing
> >> objConn.Close
> >> In the loop through the first recordset, use a couple of variables
> >> (curID, newID) to keep track of the ID being processed, and when a
> >> new ID is encountered, use the objRS2.Filter="ID=" & curID
> >> curSubtotal=objRS2("Total")
> >>
> >> I would probably use method 1
> >>
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so
> >> I don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.