Grouping Data in Aggregate Queries

[andyiain]

Hi,

I have some gloriously unweildy sql (below) and what I'd like to do is not group it by some of the items it is grouped by. I'd like to have the aggregates of

LEFT(qlup.description, Charindex (' ',qlup.description) - 1)

only. How would I go about not having to group by

r.time_stamp, b.time_stamp

or summing all of the remaining values.

If anyone has any guidence I'd be very appreciative

Regards
Andy.

SELECT COUNT(r.refnum) [# requests],
COUNT(b.reqnum) [# bank],
COUNT(bas.reqnum) [# agency],
COUNT(r.refnum) - COUNT(b.reqnum) - COUNT(bas.reqnum) [# unfilled],
CAST(SUM(CASE
WHEN Datepart(hh,r.starttime) < Datepart(hh,r.endtime) THEN (CAST(Datediff(n,r.starttime,r.endtime) AS FLOAT)) / 60
ELSE --endtime less then start start so shift gone into next day
(CAST(Datediff(n,r.starttime,Dateadd(d,1,r.endtime)) AS FLOAT)) / 60
END) AS DECIMAL(10,2)) AS [requested hours],
CAST(SUM(CASE
WHEN Isnull(b.actualhours,0) = 0 THEN CASE
WHEN Datepart(hh,b.starttime) < Datepart(hh,b.endtime) THEN (CAST(Datediff(n,b.starttime,b.endtime) AS FLOAT)) / 60
ELSE --endtime less then start start so shift gone into next day
(CAST(Datediff(n,b.starttime,Dateadd(d,1,b.endtime)) AS FLOAT)) / 60
END
ELSE (Datepart(hh,b.actualhours)) + (CAST(Datepart(n,b.actualhours) AS FLOAT) / 60)
END) AS DECIMAL(10,2)) [bank hours],
CAST(SUM(CASE
WHEN Isnull(bas.actualhours,0) = 0 THEN CASE
WHEN Datepart(hh,bas.starttime) < Datepart(hh,bas.endtime) THEN (CAST(Datediff(n,bas.starttime,bas.endtime) AS FLOAT)) / 60
ELSE --endtime less then start start so shift gone into next day
(CAST(Datediff(n,bas.starttime,Dateadd(d,1,bas.endtime)) AS FLOAT)) / 60
END
ELSE (Datepart(hh,bas.actualhours)) + (CAST(Datepart(n,bas.actualhours) AS FLOAT)) / 60
END) AS DECIMAL(10,2)) [agency hours],
SUM(b.charge) [charge],
(CASE
WHEN CAST((b.time_stamp - r.time_stamp) AS FLOAT) < 0.0001 THEN CAST(SUM(CASE
WHEN Isnull(b.actualhours,0) = 0 THEN CASE
WHEN Datepart(hh,b.starttime) < Datepart(hh,b.endtime) THEN (CAST(Datediff(n,b.starttime,b.endtime) AS FLOAT)) / 60
ELSE --endtime less then start start so shift gone into next day
(CAST(Datediff(n,b.starttime,Dateadd(d,1,b.endtime)) AS FLOAT)) / 60
END
ELSE (Datepart(hh,b.actualhours)) + (CAST(Datepart(n,b.actualhours) AS FLOAT) / 60)
END) AS DECIMAL(10,2))
ELSE 0
END) [self fill hours],
LEFT(qlup.description,Charindex(' ',qlup.description) - 1) [ass]
FROM request r
INNER JOIN qualifications_lup qlup
ON r.qualification = qlup.qualifications
LEFT OUTER JOIN bookings b
ON r.refnum = b.reqnum
LEFT OUTER JOIN agencyrequest ar
ON r.refnum = ar.bankreqnum
LEFT OUTER JOIN bookings_agencystaff bas
ON bas.reqnum = ar.reqnum

WHERE r.DATE BETWEEN @variable('Date From')
AND @variable('Date To')

GROUP BY r.time_stamp, b.time_stamp, LEFT(qlup.description,Charindex(' ',qlup.description) - 1)