Select SUM multiply different value

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)

Select SUM multiply different value

Author

Topic

CKHong
Starting Member

13 Posts

Posted - 2012-04-09 : 11:08:32

Hi,
I hope someone can help me out.
In my table I have

Bank(column) Value(column) Expected value
BankA 100 101
BankB 100 102
BankA 200 202

I want to get the SUM of all the bank with different charges.
BankA charge 1%
BankB charge 2%

My query without taking consideration of the percentage
Select Sum(IsNull(TotalCharge,0)) as TotalCharge from bank
where Bank='BankA' or Bank='BankB'

How do I select the sum of the bank include the different charge?
Hope someone can help me.

Thanks,
Brian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 11:14:18

[code]
SELECT Bank,SUM(Value),SUM(ExpectedValue)
FROM
(
SELECT Bank,Value,ExpectedValue,(ExpectedValue-Value)*100/Value AS [Charge%]
FROM table
)t
GROUP BY Bank,[Charge%]
[/code]

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

CKHong
Starting Member

13 Posts

Posted - 2012-04-09 : 11:26:55

Sorry.. i made the question wrong..

In my table I only have this 2 column

Bank(column) Value(column)
BankA 100
BankB 100
BankA 200

I need to sum up all the value based on the charges 1% and 2%
Thanks for your reply visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 11:29:48

how do you get value of charges? from another table or user input? if user input, how will user pass multiple values? also does all charges need to applied to all banks?

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

CKHong
Starting Member

13 Posts

Posted - 2012-04-09 : 11:47:40

actually i'm trying to generate a report..
so it is fixed..
BankA will be 1% and BankB will be 2%
yup.. all charges need to applied to all bank
currently I only have 2 banks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 11:49:26

hmm? your statement is contradictory

you say BankA will be 1% and BankB will be 2%
then you say all charges need to applied to all bank

what does that mean?

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

CKHong
Starting Member

13 Posts

Posted - 2012-04-09 : 11:53:08

hmm... sorry.. i think i misunderstand ur question
the end result should be
BankA 100 * 1.01 = 101
BankB 100 * 1.02 = 102
BankA 200 * 1.01 = 202
Result : 101 + 102 + 202 = 405

I don't know how to select the SUM by adding the charges which is fixed 1% for BankA and 2% for BankB

CKHong
Starting Member

13 Posts

Posted - 2012-04-09 : 11:55:39

If i do it like this

Select Sum(IsNull(TotalCharge,0)) * 1.01 as TotalCharge from bank
where Bank='BankA' or Bank='BankB'

Then it will calculate all the sum with adding 1% as the charges..
I don't know how to do it so that I can calculate the sum based on 2 charges

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 12:01:12

you still didnt answer how you will get charge values. is it from user or table?

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

CKHong
Starting Member

13 Posts

Posted - 2012-04-09 : 12:03:25

from user

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 12:17:26

please provide details on all questions asked. how do you pass multiple charge values from user as input? also how do you determine which charge needs to be associated to which bank?

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

CKHong
Starting Member

13 Posts

Posted - 2012-04-09 : 12:26:17

Actually the charge is fixed..
i was planning to put in the charges when i wrote the query..

Select Sum(IsNull(TotalCharge,0)) * 1.01 as TotalCharge from bank
where Bank='BankA' or Bank='BankB'

If i run the above query, it will set the charges 1% to all bank
I would like to know if there is any query that can let me type in the query and get the total value based on BankA fixed 1% BankB fixed 2% on the sql query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-09 : 13:29:51

for that you need to either input values from user in delimited format or store it in a table and use it

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources