SQL query with results formatted for table output?

[clone45]

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

http://www.fooindustries.com

21
455 2009-05-04

http://www.foonc.com

1871
456 2009-05-04

http://www.fooperfectmatch.com

4
457 2009-04-27

http://www.foocal.com

61

Not all domains will show up in every week. So, for example, maybe

http://www.foocal.com

had one visitor in the week of 2009-04-27. In the final report, there will still be a cell for

http://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

 

[r937]

yes, you would use a cross join and then a left outer join

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

 

[clone45]

r937, thank you so much for the detailed response! I don't actually have a domains table or a weeks table. All I have is a domain_report table which has the fields: week, domain, and visitors. So I'm most interested in your second solution.

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

 

[r937]

SELECT domains.domain
     , 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

 

[clone45]

FANTASTIC!!

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

 

[clone45]

r937,

You had it correct! I didn't copy your query properly. Great job!

 

[r937]

thanks

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon