help with query - 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

Transact-SQL (2000)

help with query

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-07 : 12:07:14

here is a glimpse of my records. What I want is a sum of the numAmount fields but I only want to include the first transaction. for example, for transactionid 16352 i would only want to add the 90.00 amount, not the others. but for the other transactions there is only one amount so i'd add those as well. understand? thanks in advance....

table: tblTransactionRegister

inttransactionid numamount dtcreated
---------------- --------- ---------
16352 90.00 2003-07-03 20:40:09.467
16352 72.00 2003-07-29 18:35:49.530
16352 57.60 2003-08-05 17:18:57.903
20552 150.00 2003-07-05 08:49:12.420
21280 150.00 2003-07-05 11:58:09.717
21336 90.00 2003-07-05 12:05:45.450

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-07 : 12:23:10

would this work for you?
select sum(t1.numamount), t1.inttransactionid
from MyTable t1 join (select inttransactionid, min(dtcreated) as dtcreated from MyTable group by inttransactionid) t2
on t1.inttransactionid = t2.inttransactionid and t1.dtcreated = t2.dtcreated
group by t1.inttransactionid

Go with the flow & have fun! Else fight the flow

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-07 : 13:10:35

don't think that's what i'm looking for

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-01-07 : 15:23:39

quote:
Originally posted by mikejohnson

don't think that's what i'm looking for

May be I don't understand your question but how about:
Select sum (select max (numamount) from tblTransactionRegister t2 where t2.nttransactionid=t1.nttransactionid) from tblTransactionRegister t1

or
select sum (a.Maxvalue) from (select max(numamount)as Maxvalue from tblTransactionRegister group by nttransactionid) a

Just my 2 cents

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-07 : 15:27:56

Mike,

Please post DDL for your table. Also post INSERT INTO statements for your sample data. Then provided the expected result set using that sample data. You've been posting a while here now, so these things should be provided to us so that we can help you and not get back "don't think that's what i'm looking for".

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources