July 20, 2011 at 5:26 am
Hi,
I have two tables with the following fields.
tSite
SiteID Int, DevloperID Int ,Site Char
tProperties
PropertyID int, SiteID int, PlotNo int, Price Money, Status Char
Status can be Available, Hold, Reserved, Sold, Completed
What i would like to do is get the following
Site, Available, Total, Hold, Total, Reserved, Total, Sold, Total, Completed, Total
FooBar, 20, £1000, 2, £20, 5, £120, 9, £300, 14, £600
So far I have this
ALTER PROCEDURE dbo.spDeveloperSites
(@DeveloperID int)
AS
SELECT SiteID,Site, [Available] , [Hold] ,[Reserved], [Sold] ,[Completed]
FROM ( SELECT dbo.tSite.SiteID, dbo.tSite.Site, dbo.tProperties.PlotNo, dbo.tProperties.Status
FROM dbo.tSite LEFT OUTER JOIN dbo.tProperties ON dbo.tSite.SiteID = dbo.tProperties.SiteID WHERE (dbo.tSite.DeveloperID = @DeveloperID) )
p
PIVOT (Count(PlotNo) FOR [Status]
IN ( [Available] , [Hold] ,[Reserved], [Sold] ,[Completed]) ) AS pvt
But I dont know how to get the totals for each status.
Hope I make sense and any help woul be great
Thank you
JB
July 20, 2011 at 1:19 pm
Try this instead of the PIVOT. See if it helps.
select site
, sum(case when [status] = 'Available' then 1 else 0 end) as Available
, sum(case when [status] = 'Available' then Price else 0 end) as Available_Total
, sum(case when [status] = 'Hold' then 1 else 0 end) as Hold
, sum(case when [status] = 'Hold' then Price else 0 end) as Hold_Total
, sum(case when [status] = 'Reserved' then 1 else 0 end) as Reserved
, sum(case when [status] = 'Reserved' then Price else 0 end) as Reserved_Total
, sum(case when [status] = 'Sold' then 1 else 0 end) as Sold
, sum(case when [status] = 'Sold' then Price else 0 end) as Sold_Total
, sum(case when [status] = 'Completed' then 1 else 0 end) as Completed
, sum(case when [status] = 'Completed' then Price else 0 end) as Completed_Total
from tProperties p
join tSite s on s.siteID = p.siteID
group by site
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 21, 2011 at 1:32 am
Works like a dream.
Thank you so much
JB
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply