Yes you are right Jochem, my mistake. I made the query so it accepted the two vars when I was testing, but I don't want to have to do that.
I want it to go through and give me all of them like you said, grouped by company_name, container_no with the factored total of each in-out*rate beside each company name, container number and the factored total of that group. -----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:193233 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
