×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL query with results formatted for table output?

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
 

RE: SQL query with results formatted for table output?

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 ...

smile

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

RE: SQL query with results formatted for table output?

(OP)
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

RE: SQL query with results formatted for table output?

CODE

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
smile

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

RE: SQL query with results formatted for table output?

(OP)
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

RE: SQL query with results formatted for table output?

(OP)
r937,

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

RE: SQL query with results formatted for table output?

thanks dazed  

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close