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
233 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
  • 3
  • 3
7 Comments
 
LVL 40

Expert Comment

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

Author Comment

by:JOPERIGO
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Awesome. Thanks so much!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 setup several different housekeeping processes for a SQL Server.

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now