SELECT 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

General SQL Server Forums

New to SQL Server Programming

SELECT QUERY

Author

Topic

sajanjacobk
Starting Member

7 Posts

Posted - 2009-07-24 : 02:24:33

I am using composite primary key.
Number SequenceNo CreditAmount DebitAmount
200 1 100.00 10.00
200 2 100.00 20.00
200 3 400.00 10.00
201 1 200.00 10.00
201 2 300.00 10.00
201 3 210.25 10.00

I need to get the sum(CreditAmount) and sum(DebitAmount) against each number.
along with Number, SequenceNo, CreditAmount and DebitAmount.

the result I need
Number SequenceNo CreditAmount DebitAmount SUM(CreditAmount) SUM(DebitAmount)
200 1 100.00 10.00 700.00 40.00
200 2 100.00 20.00 700.00 40.00
200 3 400.00 10.00 700.00 40.00
201 1 200.00 10.00 710.25 30.00
201 2 300.00 10.00 710.25 30.00
201 3 210.25 10.00 710.25 30.00

Please help thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-24 : 02:34:01

Are you using SQL 2000 or 2005/2008 ?

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-24 : 02:39:49

Hey Try this

select a.*,tab.number,tab.sum_credit,tab.sum_debit from
amount a inner join
(select number,sum(credit) as sum_credit,sum(debit) as sum_debit
from amount group by number)as tab on a.number=tab.number

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-24 : 02:40:15

[code]
DECLARE @sample TABLE
(
Number int,
SequenceNo int,
CreditAmount decimal(5,2),
DebitAmount decimal(5,2)
)
INSERT INTO @sample
SELECT 200, 1, 100.00, 10.00 UNION ALL
SELECT 200, 2, 100.00, 20.00 UNION ALL
SELECT 200, 3, 400.00, 10.00 UNION ALL
SELECT 201, 1, 200.00, 10.00 UNION ALL
SELECT 201, 2, 300.00, 10.00 UNION ALL
SELECT 201, 3, 210.25, 10.00

-- SQL 2005/2008
SELECT Number, SequenceNo, CreditAmount, DebitAmount,
TotalCredit = SUM(CreditAmount) OVER (PARTITION BY Number),
TotalDebit = SUM(DebitAmount) OVER (PARTITION BY Number)
FROM @sample

-- SQL 2000
SELECT s.Number, s.SequenceNo, s.CreditAmount, s.DebitAmount, t.TotalCredit, t.TotalDebit
FROM @sample s
INNER JOIN
(
SELECT Number,
TotalCredit = SUM(CreditAmount),
TotalDebit = SUM(DebitAmount)
FROM @sample
GROUP BY Number
) t ON s.Number = t.Number

/*
Number SequenceNo CreditAmount DebitAmount TotalCredit TotalDebit
----------- ----------- ------------ ----------- ------------ ------------
200 1 100.00 10.00 600.00 40.00
200 2 100.00 20.00 600.00 40.00
200 3 400.00 10.00 600.00 40.00
201 1 200.00 10.00 710.25 30.00
201 2 300.00 10.00 710.25 30.00
201 3 210.25 10.00 710.25 30.00

(6 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

raky
Aged Yak Warrior

767 Posts

Posted - 2009-07-24 : 02:43:40

try this

declare @temp table ( Number int, SequenceNo int, CreditAmount numeric(18,2), DebitAmount numeric(18,2) )
insert into @temp
select 200, 1, 100.00, 10.00 union all
select 200, 2, 100.00, 20.00 union all
select 200, 3, 400.00, 10.00 union all
select 201, 1, 200.00, 10.00 union all
select 201, 2, 300.00, 10.00 union all
select 201, 3, 210.25, 10.00

--select * from @temp

select t1.number,t1.sequenceno,t1.CreditAmount, t1.DebitAmount,t2.credittotal as 'sum(creiditamount)',t2.debittotal as 'sum(DebitAmount)'
from @temp t1
inner join ( select number,sum(CreditAmount) as credittotal,sum(DebitAmount) as debittotal
from @temp
group by number
) t2 on t2.number = t1.number

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-24 : 05:07:04

quote:
Originally posted by sajanjacobk

I am using composite primary key./*
Number SequenceNo CreditAmount DebitAmount
200 1 100.00 10.00
200 2 100.00 20.00
200 3 400.00 10.00
201 1 200.00 10.00
201 2 300.00 10.00
201 3 210.25 10.00
*/
I need to get the sum(CreditAmount) and sum(DebitAmount) against each number.
along with Number, SequenceNo, CreditAmount and DebitAmount.

the result I need/*
Number SequenceNo CreditAmount DebitAmount SUM(CreditAmount) SUM(DebitAmount)
200 1 100.00 10.00 600.00 40.00
200 2 100.00 20.00 600.00 40.00
200 3 400.00 10.00 600.00 40.00
201 1 200.00 10.00 710.25 30.00
201 2 300.00 10.00 710.25 30.00
201 3 210.25 10.00 710.25 30.00
*/
Please help thanks in advance.

and u will get 600 for number 200 in creditamount check it once.
use any one query for required output.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources