August 28, 2015 at 1:25 am
Your requirement isn't very clear, but if I understand correctly, you want something like this:
SELECT
PropertyID
,SUM(RentableSqFtTotal) RentableSqFtTotal
,COUNT(FloorNameID) NoofFloors
,SUM(GrossFloorSqFt) GrossFloorSqFt
,SUM(RentableSqFtTotal) - SUM(GrossFloorSqFt)
...
GROUP BY PropertyID
John
September 1, 2015 at 3:30 am
Your question is not clear enough.
Firstly you want a query to include the count for FloorNameID but the heading that you want to end up with do not include anything to do with FloorNameID.
Secondly in your INNER JOIN, it seems like table dbo.Address has no relationship with table dbo.PropertyFloor.
The order of table relationship should be correct, and in this case it should be:
FROM dbo.Address AS A WITH (nolock)
INNER JOIN dbo.Property AS P ON A.AddressID = P.PrimaryAddressID
INNER JOIN dbo.PropertyFloor PF ON P.PropertyID = PF.PropertyID
INNER JOIN dbo.FloorName FN ON PF.FloorNameID = FN.FloorNameID
The way I understand your question as written, I would do it this way:
SELECT
P.PropertyID,
P.RentableSqFtTotal,
SUM(PF.GrossFloorSqFt) GrossFloorSqFt,
P.RentableSqFtTotal - SUM(PF.GrossFloorSqFt) Difference
..........
Group by
P.PropertyID,
P.RentableSqFtTotal
Please try to build the question again so we can understand your question. Help us to understand in order to help you
September 1, 2015 at 5:22 am
From the OP, it looks like John hit the nail on the head, but if your requirements are different, please let us know.
One thing I feel necessary to point out is the nolock hint on Addresses. Please make sure you understand what that can do to your data before you use it.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.sqlservercentral.com/Forums/Topic1690995-3387-1.aspx
September 1, 2015 at 5:57 am
Thanks all Now sorted
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply