MySQL Sub-Query Help

  • Joe426
  • Beginner
  • Beginner
  • User avatar
  • Posts: 59

Post 3+ Months Ago

I have a database which stores the total cost of each customer on each date for a range of dates.

I'm trying to accomplish the following:

- For each date in the range from x to y
- plot the average total costs among all customers for each date
- however, limit the average total cost calculations to customers who's average total_costs within dates x to y are > $50/day

Screenshot of the database:
Attachments:
dailies sample.jpg

Sample of "Dailies" database



I've attempted the above with the following query, but obtained inaccurate results (see images below). I've also attached a sample of the "dailies" table in which the query references.

Code: [ Select ]
  SELECT dailies.date, dailies.cust_id, dailies.day_cost_total, dailies.date,

  (SELECT avg(dailies.day_cost_total) FROM dailies WHERE dailies.day_cost_total > 50
  AND dailies.date BETWEEN '2010-07-01' AND '2010-09-30' ) AS average_cost_total

  FROM dailies
  WHERE dailies.date BETWEEN '2010-07-01' AND '2010-09-30'
  GROUP BY dailies.date ORDER BY dailies.date ASC
  1.   SELECT dailies.date, dailies.cust_id, dailies.day_cost_total, dailies.date,
  2.   (SELECT avg(dailies.day_cost_total) FROM dailies WHERE dailies.day_cost_total > 50
  3.   AND dailies.date BETWEEN '2010-07-01' AND '2010-09-30' ) AS average_cost_total
  4.   FROM dailies
  5.   WHERE dailies.date BETWEEN '2010-07-01' AND '2010-09-30'
  6.   GROUP BY dailies.date ORDER BY dailies.date ASC


Screenshot of results:
Attachments:
query results.jpg

Results of above query.



I'm trying to avoid having to perform a MySQL call, store the data in a class/struct, then do the analysis in a script... I almost feel as if I need a loop within the query? Perhaps there is a more efficient way?

Thanks!
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Joe426
  • Beginner
  • Beginner
  • User avatar
  • Posts: 59

Post 3+ Months Ago

Anyone? Anyone at all?

Post Information

  • Total Posts in this topic: 2 posts
  • Users browsing this forum: No registered users and 117 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.