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 2008 Forums

Transact-SQL (2008)

Query

Author

Topic

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-12-05 : 11:31:57

Hi,
I have 2 tables, account and card
Account table contains accountno, date, CURRENCY
card table contains cardno, accountno, date, CURRENCY

so card table would have more than 1 card for an account and the amount for these cards would be same.

For example.

Account table

Account No Date CURRENCY
Ac1 05/12/2011 GBP
Ac2 05/12/2011 GBP

Card Table

Account No Card No Date Amount CURRENCY
Ac1 CD1 05/12/2011 100 GBP
Ac1 CD2 05/12/2011 100 GBP
Ac2 CD1 05/12/2011 200 GBP
Ac2 CD2 05/12/2011 200 GBP
Ac2 CD3 05/12/2011 200 GBP

I need to show the the sum of amount of GBP and one row per account in card table.

So the result has to be GBP 300

Kindly let me know the query please.

Thanks,
Sandesh

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-05 : 11:40:29

do you want the sum or sum by account? your 300 looks like the total sum

;with t as
(
select *, seq = row_number() over (partition by accno order by cardno) from card
)
select sum(c.amount), max(c.currency)
from t c
where c.seq = 1

;with t as
(
select *, seq = row_number() over (partition by accno order by cardno) from card
)
select a.accno, sum(c.amount), max(c.currency)
from account a
join t c
on a.accno = c.accno
where c.seq = 1
group by a.accno

or maybe just the sum of all cards per account - which sounds sensible but not what you asked for

select a.accno, sum(c.amount)
from account a
join t c
on a.accno = c.accno
group by a.accno

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 11:41:14

[code]
SELECT a.*,a1.TotalAmt
FROM Account a
INNER JOIN (SELECT [Account No],SUM(Amount) AS TotalAmt
FROM Card
GROUP BY [Account No]) a1
ON a1.[Account No] = a.[Account No]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-12-05 : 11:57:52

Thanks you. But I need the sum of amount group by currency..

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 12:00:08

hope you dont have any more things to be added to this


SELECT a.*,a1.TotalAmt
FROM Account a
INNER JOIN (SELECT [Account No],CURRENCY,SUM(Amount) AS TotalAmt
            FROM Card
            GROUP BY [Account No],CURRENCY) a1
ON a1.[Account No] = a.[Account No]
AND a1.CURRENCY = a.CURRENCY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-05 : 12:06:42

Still don't see how you can get 300 and one row per account.
Maybe back to my first suggestion and you want one row with 300 as they have the same currency - i.e. one row per account then summed per currency

;with t as
(
select *, seq = row_number() over (partition by accno order by cardno) from card
)
select sum(c.amount), c.currency
from t c
where c.seq = 1
group by c.currency

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-12-05 : 12:26:51

Thanks a lot. It worked fine...

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 13:05:17

quote:
Originally posted by sandesh.ravi

Thanks a lot. It worked fine...

Thanks,
Sandesh

which suggestion worked for you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2012-01-26 : 09:44:28

Dear Visakh,

The query provided by you worked for me. Thanks a lot.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 10:43:36

ok..No problem..glad that it sorted your problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources