I got a complicated question.
I have a query that combine data from several tables. for example:
traffic: id, traffic_source_id, first_hit_date
goals: id, goal_type, goal_date, goal_value
traffic_sources: id, source_name
goal_type could be "contact form", "demo download", "purchase" goal_value is numeric. and display the number of times the user reach the goal on the same date (day).
I'm trying to show one row for each traffic_id, and then sum every goal for this traffic_id. When a user comes for the first time, he/she gets a traffic id that stays with him (cookie). he/she can reach a goal a week later after the first hit date, and then other goal 2 days later, for example.
I want to be able to query from date > to date and show the correct sum values of the goals for that speific range. When I try to use SUM(CASE WHEN ...) I can't sum just the range withing the goals table.
example: traffic from: 1-feb to 28-feb
3 | google adwords | 0 contact | 1 demo download | 1 purchase
4 | facebook | 1 contact | 3 demo download | 3 purchase
but when I want to change the range from 1-feb to 14-feb
3 | google adwords | 0 contact | 1 demo download | 0 purchase
4 | facebook | 0 contact | 2 demo download | 2 purchase
hope I'm clear enough...
any advice will be much appreciated.
Update: query example of what I have now:
SELECT traffic.traffic_id as original_traffic_id hit_date, referrer, referrer_url, keyword, ip,
SUM(CASE goals.goal_type WHEN 'Contact' THEN 1 ELSE 0 END) goal_contact,
SUM(CASE goals.goal_type WHEN 'Download' THEN 1 ELSE 0 END) goal_download,
SUM(CASE goals.goal_type WHEN 'Signup' THEN 1 ELSE 0 END) goal_signup,
SUM(CASE goals.goal_type WHEN 'Purchase' THEN 1 ELSE 0 END) goal_purchase
FROM traffic
LEFT JOIN goals ON goals.traffic_id = traffic.traffic_id
WHERE traffic.traffic_id=100 AND hit_date >= '$from_date' AND hit_date <= '$to_date'
(where $from_date and $to_date are mysql date formats)
(this is not the real query since the original query much larger and includes about 7 more tables that joins in) This query actually sums all the goals without having into cound goal_date. I want to be able to limit the SUM to the range of $from_date and $to_date
Hope it clears it a bit more.
I don't have the answer for this yet, but I what I did is to summerize all goals with another query prior to the first one. The array looks like $goals_array[traffic_id][goal_type] -> value
And while looping through the main sql, just fetch the value from the $goals_array. It works fast, and altough I wish to make this on one query, that's ok for now.