sub query issue

Question

I have the query below which is causing me issues. It is totaling fine however as I have different service dates its grouping by distinct then servicedate, but I just want distinct districts with the totals next. How can I get round this as I'm confused now!!!

SELECT DISTINCT(DISTRICT), 
     Sum(completed) as TotalCompleted, 
     MONTHNAME,
     MONTH

FROM( SELECT  TBL_PROPERTY.DISTRICT,
     TBL_SERVICE.SERVICEDATE, 
     DATENAME(MONTH, SERVICEDATE) AS MONTHNAME,
     CONVERT(INT,CONVERT(VARCHAR,DATEPART(YEAR, SERVICEDATE) + '00')) + DATEPART(MONTH, SERVICEDATE) AS MONTH,
     COUNT(tbl_property.Propref) AS Completed

FROM TBL_SERVICE INNER JOIN TBL_PROPERTY ON TBL_SERVICE.PROPREF = TBL_PROPERTY.PROPREF 

WHERE (TBL_PROPERTY.CONTRACT ='ma2') AND 
     (LASTSERVICEDATE BETWEEN '01/jun/2009' AND DATEADD(hh,23,'15/jun/2009')) AND 
     (NOT (TBL_SERVICE.BILLCODE1 = 'NA')) AND  
     (TBL_SERVICE.STATUS = 'Serviced') AND 
     (PROPERTYCLASS = 'cont') 

GROUP BY DISTRICT, servicedate
) As sub1

GROUP BY DISTRICT, monthname, month, Completed

Example data

So for example I currently see

District1 - 2 - June District1 - 5 - June

And I want to see

District1 - 7 - June

Answer 1

I haven't checked it through thoroughly, but it looks like your problem is you have "completed" which is your aggregate in your GROUP BY at the end.

Change the last line to just as below and see if that works.

GROUP BY DISTRICT, monthname, month

Answer 2

if i read you correctly, you only want to see DISTRTICT, SUM(COMPLETED). if this is the case, you should just

 SELECT DISTRTICT, SUM(COMPLETED) FROM...

and at the end

 GROUP BY DISTRICT

edit:

with your updated request, i suggest you do the following:

 SELECT DISTRTICT, SUM(COMPLETED), MONTHNAME FROM...

and at the end

 GROUP BY DISTRICT, MONTHNAME