Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
263 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 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

963 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