SQL Count on multiple joins with dynamic WHERE

My issue is that I have a Select statement that has a where clause that is generated on the fly. It is joined across 5 tables.

I basically need a Count of each DISTINCT instance of a USER ID in table 1 that falls into the scope of the WHERE. This has to be able to be executed in one statement as well. So, Esentially, I can't do a global GROUP BY because of the other 4 tables data I need returned.

If I could get a column that had the count that was duplicated where the primary key column is that would be perfect. Right now this is what I'm looking at as my query:

SELECT  * 
FROM    TBL1 1  
        INNER JOIN TBL2 2 On 2.FK = 1.FK
        INNER JOIN TBL3 3 On 3.PK = 2.PK INNER JOIN TBL4 4 On 4.PK = 3.PK 
        LEFT OUTER JOIN TBL5 5 ON 4.PK = 5.PK 
WHERE   1.Date_Time_In BETWEEN '2010-11-15 12:00:00' AND '2010-11-30 12:00:00'
ORDER BY 
        4.Column
        , 3.Column
        , 3.Column2
        , 1.Date_Time_In DESC

So instead of selecting all columns, I will be filtering it down to about 5 or 6 but with that I need something like a Total column that is the Distinct count of TBL1's Primary Key that applies the WHERE clause that has a possibility of growing and shrinking in size.

I almost wish there was a way to apply the same WHERE clause to a subselect because I realize that would work but don't know of a way other than creating a variable and just placing it in both places which I can't do either.

Answers


If you are using SQL Server 2005 or higher, you could use one of the AGGREGATE OVER functions.

SELECT  *
        , COUNT(UserID) OVER(PARTITION BY UserID) AS 'Total'
FROM    TBL1 1  
        INNER JOIN TBL2 2 On 2.FK = 1.FK
        INNER JOIN TBL3 3 On 3.PK = 2.PK INNER JOIN TBL4 4 On 4.PK = 3.PK 
        LEFT OUTER JOIN TBL5 5 ON 4.PK = 5.PK 
WHERE   1.Date_Time_In BETWEEN '2010-11-15 12:00:00' AND '2010-11-30 12:00:00'
ORDER BY 
        4.Column, 3.Column, 3.Column2, 1.Date_Time_In DESC

Need Your Help

How to Ignore checkstyle javadoc warning for a method with a specific annotation

java annotations checkstyle

Checkstyle warns when I have a public method without javadoc, which is nice! When I override a public method I don't get the warning because the javadoc is already available in the parent class for...

UserID number not in order after deleting a user

c# asp.net

I have a table called tblUserLogin. One of the columns is labeled UserID. Evertime I add a user it numbers them in order (ex. 1, 2, 3). If I delete numbers 2 & 3 and then add another user th...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.