MySQL Sub-Query Help
- Joe426
- Beginner
- Joined: Mar 14, 2007
- Posts: 59
- Status: Offline
October 2nd, 2010, 6:39 pm
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:
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.
Screenshot of results:
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!
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:
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
(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
- 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
Screenshot of results:
Attachments:
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
- Joined: 25 Feb 2008
- Posts: ?
- Loc: Ozzuland
- Status: Online
October 2nd, 2010, 6:39 pm
- Joe426
- Beginner
- Joined: Mar 14, 2007
- Posts: 59
- Status: Offline
October 3rd, 2010, 9:57 pm
Page 1 of 1
To Reply to this topic you need to LOGIN or REGISTER. It is free.
Post Information
- Total Posts in this topic: 2 posts
- Users browsing this forum: No registered users and 193 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
