Long Query - Over my head

[Anyaer]

Bleh, two problems in one day.

I have three tables - Emails, Calls, and Clicks. I need to pull a bunch of data from Clicks within a given date range and add up those columns on a monthly basis, and get the number of entries in Calls and Emails in the same date range (also by month). The dates are stored as YYYY-MM-DD in all 3 tables, not by month, and I need to group them by month. Additionally, if there isn't an entry for that month in Clicks, but there is in Calls or Emails, I need that entry to still be in the final result.

For example, if Clicks doesn't have an entry for 2006-10, but Calls does, the result needs to have a row for 2006-10. The final result would be something like

2006-10 <click data for that month> <# of entries in Emails for that month> <# of entries in Calls for that month>
2006-10 <click data for that month> <# of entries in Emails for that month> <# of entries in Calls for that month>

So here's the basic query from Clicks as it stands right now:

SELECT DATE_FORMAT(cdate, '%Y-%m') Date, source Source, sum(imprss) Impressions, sum(cost) Cost
                        FROM Clicks
                        WHERE cdate between '2006-01-01' and '2006-12-31'
                        GROUP BY MONTH(cdate)

So I guess my first step is to tag this in preparation for a Join.

SELECT DATE_FORMAT(c.cdate, '%Y-%m') Date, c.source Source, sum(c.imprss) Impressions, sum(c.cost) Cost
                        FROM Clicks c
                        WHERE cdate between '2006-01-01' and '2006-12-31'
                        GROUP BY MONTH(cdate)

This is where I start to have trouble. I've never done a 3-way join before, especially not when I'm only trying to get a tally from the other tables instead of a pre-existing data column.

[bogu]

Try something like this:

SELECT
	DATE_FORMAT(cdate, '%Y-%m') as `date`, COUNT(*) as nr
FROM Clicks
GROUP BY `date`

and when u grab the clicks results u have something like this:

$stats = array();
while($rez = mysql_fetch_assoc($query)) {
	$stats[$rez['date']]['clicks'] = $rez['nr'];
}

when u grab the emails results u have something like this:

while($rez = mysql_fetch_assoc($query)) {
	$stats[$rez['date']]['emails'] = $rez['nr'];
}

so on and so forth ..

[Anyaer]

The problem is, it's not just a tally. The # of calls and emails needs to be matched to the proper date that they occured on, and also if an entry for a given date occurs in Calls or Emails but not Clicks, then that date needs to be created. So I can't necessarily rely on Clicks as a master list of the dates.

[Anyaer]

I think I've partly answered my own question by branching out into subqueries, which I've never written before. Or at least it seems like they could be the answer. However, the query as it stands is:

SELECT DATE_FORMAT(cdate, '%Y-%m') Date, source Source, sum(imprss) Impressions, sum(cost) Cost, sum(clicks) Clicks
                        FROM table1 p RIGHT JOIN 
           (SELECT DATE_FORMAT(cdate, '%Y-%m') Date, COUNT(company_id) Calls
           FROM table2
           WHERE `cdate` between '2006-01-01' and '2006-12-31' AND
           company_id = 2
           GROUP BY MONTH(cdate)) AS l
                        ON p.cdate = l.Date
                        WHERE `cdate` between '2006-01-01' and '2006-12-31' AND
                        company_id = 2
                        GROUP BY MONTH(cdate);

It seems like that should work. But the inner SELECT statement is apparently returning an empty set. No syntax errors are being returned by MySQL, but if I run just that inner SELECT as its own query, it in fact returns a set with data in it. Am I linking these wrong somehow?

If I run it as a LEFT JOIN, it returns the data from table1 and nothing from table2.

[bogu]

Anyaer wrote:

So I can't necessarily rely on Clicks as a master list of the dates.

And my suggestion rely on clicks? I write u only the clicks select and not the others and I told u how to parse the clicks results and the emails results cause that is your problem (how to extract all the clicks nr, emails nr and calls nr and group them by months?)...

Btw, I gave u a suggestion, I didnt write your hole script and I wont write it...

If u dont understand something, just tell me what u didnt understand ...