Subtraction of two Selects - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Subtraction of two Selects

Author

Topic

juergenkemeter
Starting Member

2 Posts

Posted - 2004-12-02 : 05:32:59

Hi,

I want to subtract the following two Queries. How does the whole SQL-Statement look like?

Select sum(CHGEBMEN) from HILFSSTO.CHCHPF where CHCHPFID = [ChargenpflegeID]
-
Select sum(CHENTMEN) from HILFSSTO.CHBEW where CHCHPFID = [ChargenpflegeID]

I tried the following:
----------------------------------------------------------------------------------
Select X.CHGEBMEN_SUM - Y.CHENTMEN_SUM
from
(Select CHCHPFID, sum(CHGEBMEN) CHGEBMEN_SUM from Hilfssto.CHCHPF group by CHCHPFID) X,
(Select CHCHPFID, sum(CHGEBMEN) CHENTMEN_SUM from Hilfssto.CHCHPF, Hilfssto.CHBEW where CHCHPFID = [ChargenpflegeID] and CHENTNDAT is not Null group by CHCHPFID) Y
where X.CHCHPFID = Y.CHCHPFID
and X.CHCHPFID = [ChargenpflegeID]
----------------------------------------------------------------------------------

which gives back this message:
-------------------------------------------
Column name CHCHPFID is ambiguous.
(SQL code = -203, SQL state = 42702)
-------------------------------------

Ciao,
Jürgen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-02 : 08:02:54

Have you tried:

SELECT (Select sum(CHGEBMEN) from HILFSSTO.CHCHPF where CHCHPFID = [ChargenpflegeID]) -
(Select sum(CHENTMEN) from HILFSSTO.CHBEW where CHCHPFID = [ChargenpflegeID])

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-03 : 02:23:35

or

Select sum(CHGEBMEN)-sum(CHENTMEN)
from HILFSSTO.CHCHPF a
join HILFSSTO.CHBEW b
on a.CHCHPFID =b.CHCHPFID
where a.CHCHPFID =[a.ChargenpflegeID]

not sure about the field ChargenpflegeID...

--------------------
keeping it simple...

juergenkemeter
Starting Member

2 Posts

Posted - 2004-12-03 : 05:14:49

Hi,
I made a mistake in the 2nd Select-Statement of my original statement. Here is the right version:
--------------------------------------------------------------------
Select X.CHGEBMEN_SUM - Y.CHENTMEN_SUM
from
(Select CHCHPFID, sum(CHGEBMEN) CHGEBMEN_SUM from Hilfssto.CHCHPF group by CHCHPFID) X,
(Select CHCHPFID, sum(CHENTMEN) CHENTMEN_SUM from Hilfssto.CHBEW group by CHCHPFID) Y
where X.CHCHPFID = Y.CHCHPFID
and X.CHCHPFID = [ChargenpflegeID]
---------------------------------------------------------------------
Thanx for your help!
/jk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources