Dan Rossi <mysql@stripped> 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
