Add a filter to my view

[Lost500]

ok this SHOULD be easy... I have a view and i would like to add a WHERE statement to exclude records that have been marked 'Expired' This is a totals view and i think that is why i'm having trouble and maybe i should use two views to get the 'Expired' records out of the dataset first, and then run my totals view. anyways here is the code that works

SELECT     TOP (100) PERCENT dbo.tblUnitInfo.UnitName, dbo.tblTractInfo.UnitTractNumber, dbo.tblTractInfo.TractName, MIN(dbo.tblLeaseInfo.ExpirationDate) 
                      AS EarliestLseExpiration, MIN(DATEDIFF(day, GETDATE(), dbo.tblLeaseInfo.ExpirationDate)) AS DaysBeforeExpiration, 
                      dbo.tblTractInfo.TractGrossAcres, SUM(dbo.tblTractLeaseAcres.ConfirmedNetAcres) AS TotalTractLeasedAcres
FROM         dbo.tblLeaseInfo INNER JOIN
                      dbo.tblTractLeaseAcres ON dbo.tblLeaseInfo.LeaseNumber = dbo.tblTractLeaseAcres.LeaseNumber INNER JOIN
                      dbo.tblTractInfo ON dbo.tblTractLeaseAcres.TractName = dbo.tblTractInfo.TractName INNER JOIN
                      dbo.tblUnitInfo ON dbo.tblTractInfo.UnitName = dbo.tblUnitInfo.UnitName
GROUP BY dbo.tblUnitInfo.UnitName, dbo.tblTractInfo.TractName, dbo.tblTractInfo.UnitTractNumber, dbo.tblTractInfo.TractGrossAcres
ORDER BY dbo.tblUnitInfo.UnitName, dbo.tblTractInfo.UnitTractNumber

now i would like to just add

WHERE dbo.LeaseInfo.Expired = 0 (or false)

(I also add it to the select statement)

When i do i get the error 'dbo.LeaseInfo.Expired is invalid in the select list because it is not contained in either an aggregate function or the group by clause'

I feel like it's not letting me filter this because its a totals query (or view i guess)

Please help i'm stuck and i would like to get it goin' thanks!

 

[Emtucifor]

You can add all the WHERE conditions you want to your query, no problem. You just can't add things to the SELECT clause without them also being grouped by.

Just make sure you put the WHERE clause after the FROM and before the GROUP BY.

If you still can't get it working, please post your failing query here so we can help you.