Aggregate function or the GROUP BY clause

[Singlem]

select CS.Ref,
		CS.C,
		DT.ID,
		DT.Init,
		DT.Surname,
		'C' [P],
		'C' [A],
		100 [M],

		case when Convert(Varchar(10),CA.A,120) is null
			then Convert(Varchar(10),CS.DC,120)
			else Convert(Varchar(10),CA.A,120) end,
			C.R,
			Convert(Varchar(10),C.CDate,120),
			Do.Outcome,
			CT.PhysicalOwner,
			Convert(Varchar(10),CS.CDate,120),
			Convert(decimal(8,2),CS.B,
			Convert(decimal(8,2),CS.CB),
			Convert(decimal(8,2),CS.IB),
			Convert(decimal(8,2),CS.Calance),
			Convert(money,SUM(dd.C)),
			Convert(money,SUM(cl.C)),
			CT.CID[Cust_ID],
			Convert(varchar(10),CS.JDate,120),
			CS.JCN,
			CO.C,
			Convert(varchar(10),CS.OD,120),
			CS.OCN,
			CO2.C
		from table1 CS
		inner join table2 DT
			on DT.DID = CS.DID
			and CS.DC is not null
		inner join table3 DO
		on DT.DID = DO.DID
		inner join ltable3 CT
			on CT.CID = CS.CID
			and CT.CD in(10, 12)
		left outer join table4 CA
			on CA.CID = CS.CID
			and CA.A not in('Gt')
		inner join table5 C
			on C.CID = CS.CID
			and C.CRMID = (select top 1 MAX(CR.CRMID) from table5 CR where CR.CD = CS.CD)  
		left outer join table6 CO
			on Co.CID = CS.JID
		left outer join table7 CQ
			on CQ.CID = CS.EID
		left outer join ltable8 dd
			on dd.ID = CS.aD
		left outer join table8 FE
			on cl.ID = C.ID
			AND CL.CS = (SELECT MAX(CL2.CS) FROM table8 KM WHERE KM.ID = CL.ID 
            AND KM.LID IN (SELECT LT.LID FROM table9 LT WHERE LT.Te LIKE 'PM%' 
			or LT.Te in ('A', 'O')))
		where (CA.A between isnull(date,CA.A) and isnull(Date,CA.A) OR CA.A IS null)

ERROR

Column 'Table' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know that I need to add a GROUP BY, but when I do that I need to group so many tables, any the query takes a very long time to run...Is there a better way to do this?

Nay ideas will help alot.

[BitBlt]

I can't honestly believe that this query runs at all... Just looking at line 52 you have an uncorrelated left join.

Anyway, you might consider doing this in two steps...select all the detail into a #TEMP table, then doing the SUMs from lines 22 and 23 against that #TEMP table. Might also be worth your time to do a ShowPlan and see where a couple of strategic indices would help.

Good luck!

[Singlem]

#Temp table?

This might sound weird but can please explain how I will do the sums agains the #Temp table. The query runs, but takes over the 8mins. And when I remove the Group By it is under 1min, but then no sum or grouping.

Got it to be a bit faster by doing the case in the frontend

[adam_k]

Subquery without the aggregate and aggregate the results:

select field1, sum(field2) from (select field1, field2 from table1) a group by field1

Just make sure that you've named all your fields in the subquery.

[BitBlt]

To the OP: you have heard of TempDB, right? You do something like:

select Field1, Field2 into #myTempTable from myTable

to create a temporary table. Then you select against it:

select Field1, sum(Field2) from #myTempTable group by Field1

That way, you get all the details, and can do all your aggregation against a single table. It's not that hard.
To adam_k:
Yes, that technique will work fine. But I figured that, due to the complexity of the original query, the temp table route might be easier to understand.