Solved

How can I pull different data for the same DB, Table, and Column in a single MS SQL Query?

Posted on 2011-03-11
7
247 Views
Last Modified: 2012-05-11
I'm still pretty new to SQL so I'm hoping this isn't as hard as it seems. I've searched here, other websites, books, etc. but I haven't found anything that will allow me to do this.

I'm writing a query for a SSRS report from IIS Log data. I need to generate a report that counts the number of unique users and the total number of page extension hits. That's the easy part. The hard part is that I need the same report to include different data when it is from the same column.

I need a total number of page extensions that only include aspx and html and another column showing the total number of page extensions that include everything except aspx and html.

Here's what I've got:

 
SELECT  srvr.CustomerName,
        COUNT(DISTINCT usr.UserID) AS UserCount,
        COUNT(page.PageExtension) AS PageViews,
        COUNT(page.PageExtension) AS HitCount

  FROM  fact_IIS_user_ip fact
  JOIN  IISLogServer srvr
    ON  fact.ServerID = srvr.ServerID
  JOIN  dim_IIS_user usr
    ON  fact.UserID = usr.UserID
   AND  usr.username != 'Not Specified'
  JOIN  dim_IIS_Page_PageExtension page
    ON  fact.PageID = page.PageID
  JOIN  dbo.dim_IIS_scstatus stat
    ON  fact.SCStatusID = stat.scstatusid
   AND  stat.scstatus IN (200, 304)

 WHERE  fact.dateid IN (SELECT CONVERT(CHAR(8),GETDATE()-1,112))
   AND  page.PageExtension IN ('aspx', 'html')
   
   GROUP BY CustomerName

Open in new window


Obviously this returns the same values for PageViews and HitCount. I've tried subqueries and such trying to get it but that has failed. The only option I can think of is to use temp tables but I'd rather not if I don't have to.
0
Comment
Question by:JOPERIGO
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35113390
What is your SQL version? Is the usr.UserID not unique for each srvr.CustomerName?
0
 

Author Comment

by:JOPERIGO
ID: 35114017
I'm using SQL 2008 R2. Yes, the UserID is a primary key and unique. Technically it is unique for each customername but the customername is in a single column in a table that is only used for adding servers to be parsed for the reports to get data from.

But the UserID is a numerical value just given, so though it's unique across the board it's not classified or easily identified with the customer.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35114031
Something like this perhaps:
SELECT srvr.CustomerName,
        COUNT(DISTINCT usr.UserID) AS UserCount,
        COUNT(page.PageExtension) AS PageViews,
        COUNT(page.PageExtension) AS HitCount,
        SUM(
			CASE page.PageExtension
				WHEN 'aspx' THEN 1
				ELSE 0
			END) TotalASPXPages,
		SUM(
			CASE page.PageExtension
				WHEN 'html' THEN 1
				ELSE 0
			END) TotalHTMLPages
 FROM   fact_IIS_user_ip fact
        JOIN IISLogServer srvr ON fact.ServerID = srvr.ServerID
        JOIN dim_IIS_user usr ON fact.UserID = usr.UserID
        JOIN dim_IIS_Page_PageExtension page ON fact.PageID = page.PageID
        JOIN dbo.dim_IIS_scstatus stat ON fact.SCStatusID = stat.scstatusid
 WHERE  fact.dateid = CONVERT(CHAR(8), GETDATE() - 1, 112)
		AND usr.username != 'Not Specified'
        AND page.PageExtension IN ('aspx', 'html')
        AND stat.scstatus IN (200, 304)
 GROUP BY CustomerName

Open in new window

0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35114037
Let me try that formatting again:
SELECT srvr.CustomerName,
        COUNT(DISTINCT usr.UserID) AS UserCount,
        COUNT(page.PageExtension) AS PageViews,
        COUNT(page.PageExtension) AS HitCount,
        SUM(
                CASE page.PageExtension
                        WHEN 'aspx' THEN 1
                        ELSE 0
                END) TotalASPXPages,
        SUM(
                CASE page.PageExtension
                        WHEN 'html' THEN 1
                        ELSE 0
                END) TotalHTMLPages
 FROM   fact_IIS_user_ip fact
        JOIN IISLogServer srvr ON fact.ServerID = srvr.ServerID
        JOIN dim_IIS_user usr ON fact.UserID = usr.UserID
        JOIN dim_IIS_Page_PageExtension page ON fact.PageID = page.PageID
        JOIN dbo.dim_IIS_scstatus stat ON fact.SCStatusID = stat.scstatusid
 WHERE  fact.dateid = CONVERT(CHAR(8), GETDATE() - 1, 112)
        AND usr.username != 'Not Specified'
        AND page.PageExtension IN ('aspx', 'html')
        AND stat.scstatus IN (200, 304)
GROUP BY 
        CustomerName

Open in new window

0
 

Author Comment

by:JOPERIGO
ID: 35131953
Thanks for the reply and the example code!

I thought about using the CASE statement but it doesn't allow me to get the two columns and totals I need in each. One column needs to be all Page Extensions of HTML and ASPX. The second column needs to be all Page Extensions excluding HTML and ASPX.

Am I missing something in your post?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 35139956
How about this:
SELECT  srvr.CustomerName,
        COUNT(DISTINCT usr.UserID) AS UserCount,
        COUNT(*) AS PageViews,
        COUNT(*) AS HitCount,
        SUM(CASE WHEN page.PageExtension IN ('aspx', 'html') THEN 1
                 ELSE 0
            END) TotalASPXHTMLPages,
        SUM(CASE WHEN page.PageExtension NOT IN ('aspx', 'html') THEN 1
                 ELSE 0
            END) TotalNotASPXHTMLPages
FROM    fact_IIS_user_ip fact
        JOIN IISLogServer srvr ON fact.ServerID = srvr.ServerID
        JOIN dim_IIS_user usr ON fact.UserID = usr.UserID
        JOIN dim_IIS_Page_PageExtension page ON fact.PageID = page.PageID
        JOIN dbo.dim_IIS_scstatus stat ON fact.SCStatusID = stat.scstatusid
WHERE   fact.dateid = CONVERT(CHAR(8), GETDATE() - 1, 112)
        AND usr.username != 'Not Specified'
        -- AND page.PageExtension IN ('aspx', 'html')
        AND stat.scstatus IN (200, 304)
GROUP BY CustomerName

Open in new window

0
 

Author Closing Comment

by:JOPERIGO
ID: 35160974
Awesome. Thanks so much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question