Sum the largest values out of 2

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

Transact-SQL (2000)

Sum the largest values out of 2

Author

Topic

kubbazoob
Starting Member

3 Posts

Posted - 2003-06-25 : 09:23:28

I have 2 tables with each 1 value ( money ). I need the sum of all money fields, but only the largest out of the 2.
Example:

table1:_______________ table2:_____________
1.MyAmountField1: 3000 MyAmountField2: 5000
2.MyAmountField1: 2000 MyAmountField2: 1000

Sum:
7000 ((MyAmountField2, Row1) + (MyAmountField1, Row2))

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-06-25 : 09:41:23

Are you looking at :

select
(tb1.colA +tb2.colB) total_sum
from
(select max(MyAmountField1) as colA from table1) tb1,
(select max(MyAmountField2) as colB from table2) tb2

Your example is a bit strange. you say the answer is 7000 ? I would have to assume that there is some kind of key (maybe the 1, 2), wheich you want to join on. Max (3000,5000) + max(2000,1000) - is that how you see it ?

In that case, it needs to be more like:

select
sum bignum
from
(select
case
when tb1.myamountfield1 >= tb2.myamountfield2
then tb1.myamountfield1
else tb2.myamountfield2
end as bignum
from table1 tb1 inner join table2 tb2 on
tb1.joinId = tb2.joinId)

does this help at all, or am I totally mis-understanding your issue ?

CiaO

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Edited by - wanderer on 06/25/2003 09:43:09

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-25 : 09:42:01

Whoa ... you need to provide a lot more here.

First, what are the keys in each of the two tables?
Second, why is your database structured this way? What business process does this model?

Jonathan
{0}

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-06-25 : 09:52:20

@Nazim - although it is really difficult to make out from what was posted, he did say he had 2 tables.

I suspect that the "data" presented sahould have been better listed as either or the following (THESE ARE GUESSES!!):

Scenario 1 (my 1st SQL)

table1:_______________
MyAmountField1
5000
3000

table2:_____________
MyAmountField2
2000
1000

Scenario 2 (my 2nd SQL)

table1:_______________
JoinID Value
1 3000
2 2000

table2:_____________
JoinID Value
1 5000
2 1000

@Kubbazoob, as SetBased says, we need more info here, dude.

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

kubbazoob
Starting Member

3 Posts

Posted - 2003-06-25 : 10:06:18

Sorry i had explained it wrong.
I have to tables:
tblOrders and tblOrderprocessing
both the tables have a field called OrderAmount and a key named OrderID.

I want to take the sum out of the tblOrders table(OrderAmount field).
BUT! if there is also an OrderAmount field filled within the tblOrderprocessing table the SUM needs to take the value out of tblOrderprocessing instead of tblOrders. So that we are always counting with the latest value known in the database.

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-25 : 10:13:59

quote:

Sorry i had explained it wrong.
I have to tables:
tblOrders and tblOrderprocessing
both the tables have a field called OrderAmount and a key named OrderID.

I want to take the sum out of the tblOrders table(OrderAmount field).
BUT! if there is also an OrderAmount field filled within the tblOrderprocessing table the SUM needs to take the value out of tblOrderprocessing instead of tblOrders. So that we are always counting with the latest value known in the database.

How do you know which is the latest value? Try something like..

SELECT
distinct
orderid,
case when oavalue > opvalue then oavalue else opvalue end as value
from
tblorders
inner join
(
SELECT
sum (orderamount) as oavalue, orderid
FROM
tblorders
group by
orderid
) orders on tblorders.orderid = orders.orderid
inner join
(
SELECT
sum (orderamount) as opvalue, orderid
FROM tblorderprocessing
group by orderid
) op on op.orderid = tblorders.orderid

Sorry that all went a bit messy.

-------
Moo.

Edited by - mr_mist on 06/25/2003 10:15:15

kubbazoob
Starting Member

3 Posts

Posted - 2003-06-25 : 10:17:51

The OrderAmount value of the tblOrderprocessing table is the latest value. This one is always added later then the OrderAmount field in the tblOrders table

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-25 : 10:23:34

SELECT
distinct
orderid,
case when opvalue is not null then opvalue else oavalue end as value
from
tblorders
inner join
(
SELECT
sum (orderamount) as oavalue, orderid
FROM
tblorders
group by
orderid
) orders on tblorders.orderid = orders.orderid
left outer join
(
SELECT
sum (orderamount) as opvalue, orderid
FROM tblorderprocessing
group by orderid
) op on op.orderid = tblorders.orderid

-------
Moo.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-06-25 : 10:42:22

Hmmm

@Mr_Mist , I think this would also work - question is, which is faster, the case or the isnull function?:

SELECT
distinct
orderid,
isnull(opvalue,oavalue) as value
from
tblorders
inner join
(
SELECT
sum (orderamount) as oavalue, orderid
FROM
tblorders
group by
orderid
) orders on tblorders.orderid = orders.orderid
left outer join
(
SELECT
sum (orderamount) as opvalue, orderid
FROM tblorderprocessing
group by orderid
) op on op.orderid = tblorders.orderid

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-25 : 10:51:01

quote:

Hmmm

@Mr_Mist , I think this would also work - question is, which is faster, the case or the isnull function?:

Probaly COALESCE (opvalue,oavalue,0)

... However, it's largely academic, as I have wrote the rest of the query so badly it will be of minimal speed difference ;)

-------
Moo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources