Dan Rossi <[EMAIL PROTECTED]> wrote on 12/29/2005 07:19:13 AM: > Thanks for your kind words of opinion, if you feel you have a better > way please do go ahead , i am going to show you the sql i ended up > using which was a union to append the current summary at the end, i > then had to use php afterwards to add up the totals as i was getting > unexpected results when grouping by month as it tended to play with the > calculations. <big snip>
Dan, I said I would help and I am offering to do so. If you would rather have someone else take over, please just say so and I will back off. This forum is the best place I have ever found for getting all kinds of crazy help and if you prefer someone else, I understand. I need some information about your table designs. Would you please post the SHOW CREATE TABLE statement results for the following tables: feed_usage, customers, feeds, producers_join, and month_totals? For example: SHOW CREATE TABLE feed_usage\G (the \G makes the output vertical, much less wrapping) I think I understand your various table relationships as you seem to set them up well in your example <table reference> clause. FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3)) AND fu.month!=DATE_FORMAT(NOW(),'%m%y') The funky numbers from your initial attempts will more than likely have been caused by several tables being joined having multiple rows of matching data. Not all data-based reports can exist as single statements. It's a limitation of the SQL language that when you want to do calculations based on the results of aggregation (SUM(), COUNT(), AVG(), etc.) , you need a second or more rounds of processing especially if you are aggregating several sets of data (usage data, bandwidth data, billing data, etc.). Temporary tables are the preferred place to store any intermediate results as they are specific to the connection that creates them (In fact the query engine generates at least one temporary table for every subquery you do). I don't want you to think I am completely against subqueries, I am not. It's just that you were not exactly using them to their full potential. If we keep our wits about us, we can get through this. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
