Eric, What about this:
(use a specific field name instead of * in the count function to save on resources - doesn't matter which one, I usually use the ID field if there is one). <cfquery name="myquery"> Select Count(fieldname) as TheCount FROM table1 WHERE datecheck UNION Select Count(fieldname) as TheCount FROM table2 WHERE datecheck UNION Select Count(fieldname) as TheCount FROM table3 WHERE datecheck </cfquery> Then in CF do: <CFSET TotalCount = ArraySum(ListToArray(myquery.TheCount)) > That gives you the total of all 3 counts together. There may be a better way to do it in SQL. You might be able to use a COMPUTE sum(Thecount) as TheTotal at the end of the query, but I'm not sure. I've never used that function, just saw it recently. Hope this helps. Dave -----Original Message----- From: Eric J. Hoffman [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 09, 2005 11:26 AM To: CF-Talk Subject: Totals from Last 7 days, multiple tables Quick question...this query is wrong, but I am in the middle of spitballing how to get this information. I am sure an old SQL hand will want to slap me and say duh..but what I am trying to get is the total of calls for each day for the last seven days...the logs of these calls exist over three separate tables. I don't know how to group the calcs in the subqueries. So maybe it should be rewritten? Here's what I have: SELECT COUNT (*) + (Select count(*) as totalcalls from cases_notes WHERE note_date BETWEEN #createodbcdate(startdat)# AND #DateAdd('D', -7, now())#) + (Select count(*) as totalcalls from customer_info WHERE logdate BETWEEN #createodbcdate(startdat)# AND #DateAdd('D', -7, now())#) as contacts, DATEPART(dd, customer_log.logdate) as dow FROM customer_log WHERE customer_log.logdate BETWEEN #createodbcdate(dateformat(Dateadd('D', -7, now()), 'mm/dd/yyyy'))# AND #DateAdd('D', 1, now())# GROUP BY DATEPART(dd, customer_log.logdate) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:214213 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
