Thanks Jochem! It is nice to have one of the CF Gods answer my silly little question so fast. You Rock!
That thing had me banging my head against the CRT! ~Michael -----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Sunday, February 06, 2005 12:55 PM To: CF-Talk Subject: Re: Query with Aggregate Functions WebStop Internet Services wrote: > I have a query that I made that will accept a variable to give the results I > need. The only problem is, I have to specify the CONTAINER_NO and > COMPANY_NAME each time and I need to do an aggregate function in here > somewhere that will take the field "OUT" and subtract it from "IN", then > multiply it by "RATE" and group the results by Company_Name, > Container_Number. IN usually is a keyword in databases. Can you still change that name? > How do I make it so that the output gives distinct records with COMPANY_NAME > and CONTAINER_NO along with the SUM of IN minus OUT * RATE and list each > grouped recordset with the product of the IN-OUT*RATE calculation. That does not make sense. If you specify the container_no and company_name like you say above then you will obviously not get distinct records. > OUTPUT LIKE THIS: > > FIELDS>>> COMPANY_NAME CONTAINER_NO TOTAL RATE > ---------------------------------------------------------------------------- > ------ > VALUES>>> Joe's Company FWDE44563798764 $200.00 > VALUES>>> Joe's Company DREE44563798764 $400.00 > VALUES>>> Fred's Company BGFE44563798764 $300.00 > VALUES>>> Jan's Company F555544563798764 $600.00 > VALUES>>> Jan's Company 543DE44563798764 $800.00 > VALUES>>> Bert's Company vfdDE44563798764 $1000.00 I think you want: SELECT company_name, container_no, SUM(("IN" - out) * rate) AS total_rate FROM rate_calc GROUP BY company_name, container_no If that doesn't work, please include your table schema. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193234 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
