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
244 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

821 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