summing info from a table - 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

Other Forums

MS Access

summing info from a table

Author

Topic

jtabb
Starting Member

2 Posts

Posted - 2004-07-08 : 13:55:49

I have a table that gives detailed information about what is located in certain rooms of a large building. It includes columns of positive and negative values for each entry. I would like for a query to perform the following process:

Table: Terminal Units

Rm# Max Min
1.16 -445 -240
1.16 350 145
1.20 -560 -150
1.20 695 285
etc.

for room 1.16: -445+350=-95 and -240+145=-95
-95-(-95)= 0 so it is correct

for room 1.20: -560+695=135 and -150+285=135
135-135= 0 so it is correct

Because I have over 100 rooms in my table, a query that does this would be very helpful.

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 14:11:08

[code]
use northwind
go

CREATE TABLE MyTable
(
[Rm#] float,
[Max] int,
[Min] int
)
GO

INSERT INTO MyTable
SELECT 1.16, -445, -240 UNION ALL
SELECT 1.16, 350, 145 UNION ALL
SELECT 1.20, -560, -150 UNION ALL
SELECT 1.20, 695, 285
Go

SELECT P.MAX, P.MIN, N.MAX, N.MIN,
N.MAX+P.MAX, N.MIN+P.MIN,
(N.MAX+P.MAX) - (N.MIN+P.MIN)
FROM MyTable P
JOIN MyTable N
ON N.[Rm#] = P.[Rm#]
AND N.MAX < 0
WHERE P.MAX > 0
GO
DROP TABLE MyTable
GO
[/code]

jtabb
Starting Member

2 Posts

Posted - 2004-07-08 : 15:32:59

Unfortunately I do not have northwind installed and I am not privileged enough to install it myself on this computer (I am at work). I was trying to either do it in Design View or SQL. Is this possible?

I appreciate you taking the time to help me!

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 18:44:36

My example doesn't rely on anything in Northwind, I was just illustrating an example in a Playground Environment!

You've already got the data so you should be able to just go with the final SELECT statement - change the Table and Column names to match what you have.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources