Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
260 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 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…

721 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