Grouping and summing

[DebbieDavis]

Hi there,

I have an estimate file that gets printed for customers. The file consists of a verbiage line like ROOF then the estimated items follow then another category like FIRST FLOOR and more estimated items, etc. I'd like to be able to get a subtotal between the verbiage lines but I have no idea how to accomplish this. The verbiage lines have a line code of "V" and the corresponding items have a line code of "N" under each "V". So I would want to add up all of the "N" item costs under each "V". Is this possible? any thanks in advance.

 

[trollacious]

Show your code.

 

[DebbieDavis]

No code to show, that's my problem. Here's an example of what I would like to display though...

V ROOF
N NAILS 5.00
N PLYWOOD 150.00
SUBTOTAL ROOF 155.00

V FIRST FLOOR
N MORE NAILS 15.00
N MORE PLYWOOD 75.00
SUBTOTAL FIRST FLOOR 90.00

select LC, ITEM, PRICE from est2 where estno=2968

LC = N or V
ITEM = i.e. NAILS
PRICE = price

What type of query could I write that would group by V codes and total the N codes that fall under those V codes? Thanks very much.

 

[trollacious]

Is this a homework assignment?

 

[DebbieDavis]

Not hardly. Pretty elementary to you, I guess. I've received so much helpful information from this site by reading posts and posting myself and actually helping others when I can (imagine that) but never had anybody ask me if this is a homework assignment. Sometimes I take the "long way home" so I posted in hopes somebody would have an elegant idea, but nevermind. I'll pour over it and figure it out. Thanks.

 

[DreXor]

reason he asked is the "context" seems like a school work assignment, and this site prohibits school work.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[DebbieDavis]

Thanks Drexor. I understand. Not used to such curt responses, at least not in this forum. I was trying to keep the example simple. The item descriptions are more like this: 1X4-14' ROUGH SAWN SPF S1S2E. Thanks again.

 

[trollacious]

The reason I asked if this were a homework assignment was that when I wanted you to show your code, you had nothing written but some ideas what you wanted it to turn out at. This is often the case with students looking for someone to do their assignments for them. They come with the homework problem, not having written anything on their own, and want someone to write the whole thing for them to turn in.

 

[DebbieDavis]

I understand. I figured it out. It isn't very pretty, kind of clunky (my norm) but I stuck with it and it's working for me:

set rsPB=objConn.execute("SELECT SUM(extension) as ordertotal,SUM(pcs) as totalitems,item_no,lc,description FROM est2 where (estno=2968) GROUP BY item_no,LN,lc,description, line_no ORDER BY line_no")

	if not rsPB.eof then
	Response.Write "<hr>estimate for 2968"
	Response.Write "<table border=1 width='100%'>"'<tr><th>Product<th>Cost"
	
oldCat=""
theCat=""    
catTotal=0

	do while not rsPB.eof
   theCat=rsPB("lc")
   theProduct=rsPB("item_no")
   totalSale=rsPB("ordertotal")

   if oldCat<>theCat then
   if catTotal>0 then
      response.write "<tr><td colspan=3 align=right><font color=red>Total for category:<td align=right>" & formatnumber(catTotal,2) & "<td align=right></font>" & formatnumber(catTotal-catCost,2)
   end if
      Response.Write "<tr><td colspan=5><strong><u>" & rsPB("description") & "</u></strong>"
      catTotal=totalSale
      catCost=totalCost
      oldCat=theCat
   else
      catTotal=catTotal + totalSale
      catCost=catCost + totalCost
   end if
	
	cname=rsPB("item_no")
	totalitems=rsPB("totalitems")
	if totalitems>0 then
	totalitems=totalitems
	else
	totalitems=0
	end if
	ordertotal=rsPB("ordertotal")
	if ordertotal>0 then
	ordertotal=ordertotal
	else
	ordertotal=0
	end if
	oldCat=theCat
	rsPB.movenext
	loop
    response.write "<tr><td colspan=3 align=right><font color=red>Total for category:<td align=right>" & formatnumber(catTotal,2) & "<td align=right></font>" & formatnumber(catTotal-catCost,2)
	Response.Write "</table>"
	end if

What do you think? Is there a way to trim it down and make it better? Many thanks again for your ideas!

 

[Tarwn]

I don't see any description for the relationship between verbiage lines and itemized lines. The problem being that ifyour depending on the order in the database to decide what items belong to which verbiage, your going to have some problems.

Also, I am unsure whether you have already built a database of this informaiton or simply need the output files in a certain format and have free rein to build the database in any fashion you would like (or if your trying to forgo the db completely and just read from text files).

I guess what I am aiming at is the need for more info
Ie, is the data already being stored in a database, how items are tied to their respective floors/verbiage, etc.

-T

 

[DebbieDavis]

The data is already being stored in a database. The lines are numbered by the line_no field, so they are in line_no order in my query. That's the only relationship I have, so whatever falls under the V in line_no order is what gets totalled. I've checked it in Excel though and it's adding up properly. I checked some others as well and they add up too. Thanks.