Better way to do Sum on multiple rows??

[chicdog]

Is there a better than using a cursor to move through a record set? What I need to do is in order to get the total cost for a customer's hotel room cost when he/she has booked two or more rooms for our convention.


DECLARE RoomTotalCursor CURSOR FOR
SELECT CONVERT(money, (dbo.tblRooms.TotalNights * dbo.tblRoomTypes.RoomRatetoAttendee) * (1 + dbo.tblRoomTypes.RoomSalesTax)+ dbo.tblRooms.TotalNights * dbo.tblRoomTypes.RoomGratuity *dbo.tblRooms.Occupants + dbo.tblRooms.TotalNights * dbo.tblRoomTypes.ResortLevy * dbo.tblRooms.Occupants + dbo.tblRooms.TotalNights * dbo.tblRoomTypes.RoomAdjFee) AS Total
FROM dbo.tblRooms INNER JOIN dbo.tblRoomTypes ON dbo.tblRooms.RoomKey = dbo.tblRoomTypes.RoomKey
WHERE (dbo.tblRooms.AttendeeKey = @AttendeeKey)
open RoomTotalCursor
FETCH NEXT FROM RoomTotalCursor into @MyTotal
WHILE @@FETCH_STATUS=0
BEGIN
Select @RoomTotal = @RoomTotal + @MyTotal
FETCH NEXT FROM RoomTotalCursor into @MyTotal
END
CLOSE RoomTotalCursor
DEALLOCATE RoomTotalCursor

Thanks,
Bryan

 

[sunila7]

Does this give wht u r looking for?

SELECT
SUM(CONVERT(money, (dbo.tblRooms.TotalNights * dbo.tblRoomTypes.RoomRatetoAttendee) * (1 + dbo.tblRoomTypes.RoomSalesTax)+ dbo.tblRooms.TotalNights * dbo.tblRoomTypes.RoomGratuity *dbo.tblRooms.Occupants + dbo.tblRooms.TotalNights * dbo.tblRoomTypes.ResortLevy * dbo.tblRooms.Occupants + dbo.tblRooms.TotalNights * dbo.tblRoomTypes.RoomAdjFee)) AS Total
FROM dbo.tblRooms INNER JOIN dbo.tblRoomTypes ON dbo.tblRooms.RoomKey = dbo.tblRoomTypes.RoomKey
WHERE (dbo.tblRooms.AttendeeKey = @AttendeeKey)

Sunil

 

[chicdog]

No, it doesn't give me a total for the column it just gives the total for the row.

 

[chicdog]

I figured out what I needed to do. I used WITH ROLLUP. For anyone who needs an example:
SELECT SUM(dbo.tblEvents.FunctionFee)
FROM dbo.tblAttendEvent INNER JOIN dbo.tblEvents ON dbo.tblAttendEvent.EventKey = dbo.tblEvents.EventKey
GROUP BY dbo.tblAttendEvent.AttendeeKey WITH ROLLUP
HAVING (dbo.tblAttendEvent.AttendeeKey = @AttendeeKey)