Sub Query Help

Question

Hi I have the below query which i want to det the distinct districts and how sum of how many properties are for each one but i cant remember how to do the sub query!!!

Can any one help please

SELECT DISTRICT, PROPREF

FROM TBL_PROPERTY 

WHERE  (CONTRACT = 'ma2') AND 
     (LASTSERVICEDATE <= DATEADD(HH,23,CONVERT(DATETIME,'30/05/2009', 103))) AND 
     (SERVICESTATUS = 'SERVICED')) AS Sub1


GROUP BY DISTRICT, OFFICE, PROPREF

Answer 1

I think you want this:

select DISTRICT, count(PROPREF)
from TBL_PROPERTY
where CONTRACT = 'ma2'
    and LASTSERVICEDATE <= DATEADD(HH,23,CONVERT(DATETIME,'30/05/2009', 103))
    and SERVICESTATUS = 'SERVICED'
group by DISTRICT

If you also wanted it broken out by office, you'd add OFFICE to the select and group by clauses.

Answer 2

SELECT COUNT(*) AS PROPERTY_COUNT ...

Is that what you're looking for?

Answer 3

just figured it out sorry if anyone responded!! Also forgot to take office out i think which i got rid of in end.

SELECT DISTINCT(DISTRICT), SUM(services) AS TOTALSERVICES

FROM 

(SELECT DISTRICT, COUNT(PROPREF) AS Services

FROM TBL_PROPERTY 

WHERE  (CONTRACT = 'ma2') AND 
     (LASTSERVICEDATE <= DATEADD(HH,23,CONVERT(DATETIME,'30/05/2009', 103))) AND 
     (SERVICESTATUS = 'SERVICED')
GROUP BY DISTRICT, propref

) as sub1

GROUP BY DISTRICT, services