Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL query with results formatted for table output?

Status
Not open for further replies.
clone45

clone45

Programmer
Mar 28, 2006
22
US
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 21
455 2009-05-04 1871
456 2009-05-04 4
457 2009-04-27 61

Not all domains will show up in every week. So, for example, maybe had one visitor in the week of 2009-04-27. In the final report, there will still be a cell for 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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
:)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
r937,

You had it correct! I didn't copy your query properly. Great job!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
415
carp
carp
mmiah1982
  • Locked
  • Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
367
mmiah1982
mmiah1982
Tetol
  • Locked
  • Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
380
carp
carp
mikrom
  • Locked
  • Question
from a table with symmetric relation select every pair only once
Replies
0
Views
291
mikrom
mikrom
fredong
  • Locked
  • Question
Update upper row data with next row data that was detached partially from import.
Replies
6
Views
549
carp
carp

Part and Inventory Search

Sponsor

Back
Top