Summing rows into a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Summing rows into a column

Author

Topic

steve_ignorant
Starting Member

6 Posts

Posted - 2006-03-16 : 14:11:19

Hello, This is my first post so please be kind. I have been attempting to convert a query I built in MS Access for use in MSSQL 2000, the syntax for these is different so I was frustrated to find out I could not use the access query.

I have 4 columns one containing a user Id and the others costs, I wish to total the costs per user ID at the end of each row.

So far I have managed to convert about half of my access query, this gives mev the clientID's and costs in columns but I cannot for the life of me get the costs in a total. It's annoying because my access query works perfectly.

This is my Access query:

SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.CostPerNight,
Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings!Booking_Cost+Room_Facilities!FacilityCost AS TotalCost,
[TotalCost]*17.5/100+[TotalCost] AS [Total+VAT]
FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;

And this is what I have been able to salvage into MSSQL format:

SELECT 
  Holiday_Bookings.ClientID,
  Holiday_Bookings.Booking_Cost,
  Rooms.CostPerNight,
  Room_Facilities.FacilityCost
FROM
  Rooms
  INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
  INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)

How can I total the three columns and add the tax?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-16 : 14:30:19

I'm not much of an access guy but does this work? (you may need to tweek the calculation parenthisis)


select	ClientID
	,Booking_Cost
	,FacilityCost
	,CostPerNight
	,TotalCost
	,(([TotalCost] * 17.5) / 100) + [TotalCost] AS [Total+VAT]
from	(
	select	hb.ClientID
		,hb.Booking_Cost
		,rf.FacilityCost
		,r.CostPerNight
		,(r.CostPerNight * Nights_Stayed) + hb.Booking_Cost + rf.FacilityCost AS TotalCost
		--,[TotalCost] * 17.5/100 + [TotalCost] AS [Total+VAT]
	from	Rooms r
	JOIN	Room_Facilities rf
			ON rf.FacilityID = r.FacilityID
	JOIN	Holiday_Bookings hb
			ON hb.ClientID = r.Clients_ID
	) a

EDIT:
woops, i meant to comment out the last calculated column in the derived table.

Be One with the Optimizer
TG

steve_ignorant
Starting Member

6 Posts

Posted - 2006-03-16 : 16:15:47

Thanks but I am looking for sql code that will work on mssql server, I have a working access database. I think you misunderstood as the code you posted included access code.

Can I try to explain again I have this portion of sql that works that is the code below. I want the columns Booking_Cost,CostPerNight*Nights_Stayed,FacilityCost totalled so the row at the end shows the amount owed.

SELECT 
  Holiday_Bookings.ClientID,
  Holiday_Bookings.Booking_Cost,
  Rooms.CostPerNight,
  Room_Facilities.FacilityCost
FROM
  Rooms
  INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
  INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-16 : 16:32:23

>>I think you misunderstood as the code you posted included access code.

Did you try it?
I didn't misunderstand. it should work in ms sql server as long as the table names and column names are correct.

EDIT:
woops - I meant to comment out the last column in the derived table. I edited my original post...

look, no ms access up my sleeve:


set nocount on
create table #rooms (facilityid int, costpernight money, nights_stayed int, clients_id int)
create table #Room_Facilities (facilityid int, facilityCost money)
create table #Holiday_Bookings (clientid int, booking_cost money)

insert #rooms values (1, 50, 2, 10)
insert #room_facilities values (1, 20)
insert #holiday_bookings values (10, 25)

select	ClientID
	,Booking_Cost
	,FacilityCost
	,CostPerNight
	,TotalCost
	,(([TotalCost] * 17.5) / 100) + [TotalCost] AS [Total+VAT]
from	(
	select	hb.ClientID
		,hb.Booking_Cost
		,rf.FacilityCost
		,r.CostPerNight
		,(r.CostPerNight * Nights_Stayed) + hb.Booking_Cost + rf.FacilityCost AS TotalCost
		--,[TotalCost] * 17.5/100 + [TotalCost] AS [Total+VAT]
	from	#Rooms r
	JOIN	#Room_Facilities rf
			ON rf.FacilityID = r.FacilityID
	JOIN	#Holiday_Bookings hb
			ON hb.ClientID = r.Clients_ID
	) a



drop table #rooms
drop table #room_facilities
drop table #holiday_bookings


output:

ClientID    Booking_Cost          FacilityCost          CostPerNight          TotalCost             Total+VAT     
----------- --------------------- --------------------- --------------------- --------------------- --------------
10          25.0000               20.0000               50.0000               145.0000              170.375000000

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources