You could try doing a subquery. SELECT EmpID, SUM(SalesAmount) AS SalesSum, (SELECT name FROM EmpInfo where EmpID = tblSales.EmpID) as employee_name
FROM tblSales WHERE quarter = 1 GROUP BY EmpID HAVING SUM(SalesAmount) > 1 ORDER BY SUM(SalesAmount) DESC ______________________ steve oliver atnet solutions, inc. http://www.atnetsolutions.com -----Original Message----- From: Dave Douglas [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 05, 2002 7:59 PM To: CF-Talk Subject: SQL- Tricky Group By and Hello, I am putting together reports for the Sales teams in Cold Fusion and need to incorporate 2 separate tables in my query. Currently I am using a query of: SELECT EmpID, SUM(SalesAmount) AS SalesSum FROM tblSales WHERE quarter = 1 GROUP BY EmpID HAVING SUM(SalesAmount) > 1 ORDER BY SUM(SalesAmount) DESC and it is returning multiple ID's with their Sales Sum, what I need to do is get the Employee Name and their Quota from the EmpInfo Table so I can do quota % calculations and also show the Employee name as opposed to just ID. Every Join I try is giving me errors and I was curious to see what others take on the query was? Any other advice on using CF for Reports? Thanks for your help Dave Woodbridge, NJ ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
