Select foo1.Region, foo1.County, foo1.Schooldistrict, 
isnull(sum(case
	when foo2.status = 'Active' then 1
end), 0) as Active,
isnull(sum(case
	when foo2.status = 'Pending' then 1
end), 0) as Pending,
isnull(sum(case
	when foo2.status = 'Sold' then 1
end), 0) as Sold
from foo1 inner join foo2 on foo1.region = foo2.region and foo1.id = foo2.id
group by foo1.Region, foo1.County, foo1.Schooldistrict

Cincy   Butler   Edgewood SD    2    3    1
Cincy   Butler   Hamilton SD   15   20   13
Cincy   Butler   Westwood SD    5    4    0
Cincy   Butler   Other         51   34   50

quote:

---

Originally posted by rattlehead02

I did some looking into CTE. I managed to create my query as a CTE, the managed to select * from myCTE. I read that CTE is update-able so instead of the select statement I tried

update myCTE set SchoolDistrict = 'Other' where 'Active' + 'Pending' + 'Sold' < 10

But got an error telling me that "Cannot update the view or function "myCTE" because it contains aggregates or a DISTINCT clause."

So, any tips on what next now that I have my table as a CTE?

---

USE [My DB]
GO
WITH myCTE (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t)
as
(
SELECT     mls_data.mlssource, mls_data.county, mls_data.schooldistrict, mls_data.new,
isnull(sum(case
	when status.status = 'Active' and dateaddedmonth = month('7/1/2011') and dateaddedyear = year('7/31/2011') then 1
end), 0) as Active,
isnull(sum(case
	when status.status = 'Pending' and dateaddedmonth = month('7/1/2011') and dateaddedyear = year('7/31/2011') then 1
end), 0) as Pending,
isnull(sum(case
	when status.status = 'Sold' and sellingdateyear = year('6/1/2011') and sellingdatemonth = month('6/1/2011') then 1
end), 0) as CurrMthClose,
isnull(sum(case
	when status.status = 'Sold' and sellingdateyear = year('6/1/2011') and sellingdatemonth <= month('6/1/2011') then 1
end), 0) as YTDClose,
isnull(sum(case
	when status.status = 'Sold' and sellingdateyear = year('6/1/2010') then 1
end), 0) as Year1Closed,
isnull(sum(case
	when status.status = 'Sold' and sellingdateyear = year('6/1/2009') then 1
end), 0) as Year2Closed,
isnull(sum(case
	when status.status = 'Sold' and sellingdateyear = year('6/1/2008') then 1
end), 0) as Year3Closed,
isnull(sum(case
	when status.status <> 'Sold' and dateaddedmonth = month('7/1/2011') and dateaddedyear = year('7/31/2011') then 1
end), 0) as CurrentMonthActive,
isnull(sum(case 
	when status.sellingdate >= '7/1/2010' and status.sellingdate < '7/1/2011' then 1
end), 0) as Prev12MonthsSold,
isnull(sum(case
	when status.status = 'Active' and dateaddedmonth = month('7/1/2011') and dateaddedyear = year('7/31/2011') then status.listingprice
end), 0) as Active$,
isnull(sum(case
	when status.status = 'Pending' and dateaddedmonth = month('7/1/2011') and dateaddedyear = year('7/31/2011') then status.listingprice
end), 0) as Pending$,
isnull(sum(case
	when status.status = 'Sold' and sellingdateyear = year('6/1/2011') and sellingdatemonth = month('6/1/2011') then status.sellingprice
end), 0) as CurrMthClose$,
isnull(sum(case
	when status.status = 'Sold' and sellingdateyear = year('6/1/2011') and sellingdatemonth <= month('6/1/2011') then status.sellingprice
end), 0) as YTDClosing$,
isnull(sum(case
	when status.status = 'Sold' and sellingdateyear = year('6/1/2010') then status.sellingprice
end), 0) as Year1$,
isnull(sum(case
	when status.status = 'Sold' and sellingdateyear = year('6/1/2009') then status.sellingprice
end), 0) as Year2$,
isnull(sum(case
	when status.status = 'Sold' and sellingdateyear = year('6/1/2008') then status.sellingprice
end), 0) as Year3$
FROM         mls_data INNER JOIN
                      Status ON mls_data.listingnumber = Status.listingnumber AND mls_data.mlssource = Status.mlssource
where status.dateadded between '1/1/2008' and '7/31/2011' and mls_data.county = 'Butler' and mls_data.mlssource = 'Cincy'
group by mls_data.new,mls_data.mlssource, mls_data.county, mls_data.schooldistrict
)
select a, b, case when e+f+g+h+i+j+k+l+m < 10 then 'Other' else c end as c, d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t
from myCTE
group by d,a,b,c
order by d,a,b,c