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:
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
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.