SQL query with results formatted for table output?
SQL query with results formatted for table output?
(OP)
Hello,
I'm building a report for a client who wants to know how many visitors are reaching his site based on the domain name entered by the visitors. For instance, some visitors may type in "foo.domain.com","domain.com", or "mobile.domain.com". The report needs to be broken down by week and should display the last 10 weeks of data.
Here's some more information about the report:
- The report will have the weeks as column headers
- The report will have the domains as row labels
- The cells of the report will contain the number of visitors
I've already spent some time on the project and have built a summary table specifically for this report that looks like this:
domain_report
=============
id: int, primary key, auto-increment
week: date
domain: varchar(255)
visitors: int(11)
Some sample data looks like:
454 2009-05-04 www.fooindustries.com 21
455 2009-05-04 www.foonc.com 1871
456 2009-05-04 www.fooperfectmatch.com 4
457 2009-04-27 www.foocal.com 61
Not all domains will show up in every week. So, for example, maybe www.foocal.com had one visitor in the week of 2009-04-27. In the final report, there will still be a cell for www.foocal.com for 2009-04-27, but it will be 0.
Normally to generate the report I would build up a big associative array in PHP, but it would be messy since I would have to 1) query for unique weeks, 2) query for unique domains, 2) build up a big associative array like data[$domain][$week] = visits, and fill the associative array with data from a third query.
Is there some sort of self-outer-join that I can do to fetch the domains, weeks, and visitors that would translate well to a spreadsheet style report, considering that not all weeks have each domain listed?
Thanks!
- Bret
I'm building a report for a client who wants to know how many visitors are reaching his site based on the domain name entered by the visitors. For instance, some visitors may type in "foo.domain.com","domain.com", or "mobile.domain.com". The report needs to be broken down by week and should display the last 10 weeks of data.
Here's some more information about the report:
- The report will have the weeks as column headers
- The report will have the domains as row labels
- The cells of the report will contain the number of visitors
I've already spent some time on the project and have built a summary table specifically for this report that looks like this:
domain_report
=============
id: int, primary key, auto-increment
week: date
domain: varchar(255)
visitors: int(11)
Some sample data looks like:
454 2009-05-04 www.fooindustries.com 21
455 2009-05-04 www.foonc.com 1871
456 2009-05-04 www.fooperfectmatch.com 4
457 2009-04-27 www.foocal.com 61
Not all domains will show up in every week. So, for example, maybe www.foocal.com had one visitor in the week of 2009-04-27. In the final report, there will still be a cell for www.foocal.com for 2009-04-27, but it will be 0.
Normally to generate the report I would build up a big associative array in PHP, but it would be messy since I would have to 1) query for unique weeks, 2) query for unique domains, 2) build up a big associative array like data[$domain][$week] = visits, and fill the associative array with data from a third query.
Is there some sort of self-outer-join that I can do to fetch the domains, weeks, and visitors that would translate well to a spreadsheet style report, considering that not all weeks have each domain listed?
Thanks!
- Bret
RE: SQL query with results formatted for table output?
SELECT ...
FROM domains
CROSS
JOIN weeks
LEFT OUTER
JOIN visitorcounts
ON visitorcounts.domain = domains.domain
AND visitorcounts.week = weeks.week
note that this will give you the data pretty much the same way as the sample data you posted, so to get it into a spreadsheet style layout, you'd have to do that with php
also, you don't actually have to have a domains table or a weeks table, although that might be advantageous
you can generate the domains and weeks "on the fly" using derived tables --
SELECT ...
FROM ( SELECT DISTINCT domain
FROM visitorcounts ) AS domains
CROSS
JOIN ( SELECT DISTINCT week
FROM visitorcounts ) AS weeks
LEFT OUTER
JOIN ...
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: SQL query with results formatted for table output?
Just to reiterate your solution with some minor changes:
SELECT ...
FROM ( SELECT DISTINCT domain
FROM domain_report ) AS domains
CROSS
JOIN ( SELECT DISTINCT week
FROM domain_report ) AS weeks
LEFT OUTER
JOIN ...
Part of this works great and returns a list of dates and domains, but is missing the "visitors" data:
SELECT *
FROM (
SELECT DISTINCT domain
FROM domain_report
) AS domains
CROSS JOIN (
SELECT DISTINCT week
FROM domain_report
) AS weeks
ORDER BY week
Now how do I include the "visitors" column in my results? You left me a great clue: LEFT OUTER JOIN..., but since I'm using derived tables, I don't know the syntax for the LEFT OUTER JOIN. I'm using MySQL. Any help would be much appreciated.
Thanks,
- Bret
RE: SQL query with results formatted for table output?
CODE
, weeks.week
, domain_report.visitors
FROM ( SELECT DISTINCT domain
FROM domain_report ) AS domains
CROSS
JOIN ( SELECT DISTINCT week
FROM domain_report ) AS weeks
LEFT OUTER
JOIN domain_report
ON domain_report.domain = domains.domain
AND domain_report.week = weeks.week
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: SQL query with results formatted for table output?
Your solution worked perfectly, except that some of the columns were duplicated in my results. It was pretty easy to solve though. Here's the final query. Thanks for teaching me about CROSS JOIN!
SELECT domains.domain,weeks.week, visitors
FROM ( SELECT DISTINCT domain
FROM domain_report ) AS domains
CROSS
JOIN ( SELECT DISTINCT week
FROM domain_report ) AS weeks
LEFT OUTER
JOIN domain_report
ON domain_report.domain = domains.domain
AND domain_report.week = weeks.week
RE: SQL query with results formatted for table output?
You had it correct! I didn't copy your query properly. Great job!
RE: SQL query with results formatted for table output?
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon