Need Help with SUM Query

[anonymous]

I need a help with forming a query for the following table..

Customerid Amnt Mode
1 200 Internet
2 100 Internet
3 900 ATM
1 300 ATM
1 90 Internet
4 10000 ATM
2 500 ATM
3 100 Internet

I want a query which will get me the sum of amount for all modes and sum of amount for Internet mode alone for all customers..I want the result in one row itself...i.e., something like the following

Customerid TotalAmnt InternetAmt
1 590 290
2 600 100
3 1000 100
4 10000 0

Please help in querying for this..

[azimmer]

I need a help with forming a query for the following table..

Customerid Amnt Mode
1 200 Internet
2 100 Internet
3 900 ATM
1 300 ATM
1 90 Internet
4 10000 ATM
2 500 ATM
3 100 Internet

I want a query which will get me the sum of amount for all modes and sum of amount for Internet mode alone for all customers..I want the result in one row itself...i.e., something like the following

Customerid TotalAmnt InternetAmt
1 590 290
2 600 100
3 1000 100
4 10000 0

Please help in querying for this..

select customerid, sum(Amnt) as TotalAmount, sum(Amnt * (case Mode when 'Internet' then 1 else 0 end))
from myTable
group by customerid

[ck9663]

select customerid, sum(Amnt) as TotalAmount, sum(Amnt * (case Mode when 'Internet' then 1 else 0 end))
from myTable
group by customerid

will this produce the same result?


select customerid, sum(amnt) as totalamount
from myTable
where Mode = 'Internet'
group by customerid

[azimmer]

will this produce the same result?


select customerid, sum(amnt) as totalamount
from myTable
where Mode = 'Internet'
group by customerid

Not really: it only shows the Internet amounts for each customer but not a total amount.

[ck9663]

Not really: it only shows the Internet amounts for each customer but not a total amount.

my bad, then your's is correct

[anonymous]

Hi azimmer,
Thanks for the query. It worked exactly the way i wanted..
I needed one more help..In the query we have only one condition to check if mode is 'Internet' alone..If i have one more condition to be checked like if mode is 'Internet' and Type is 'deposit' and another condition like mode is 'Internet' and type is 'Withdrawal'.. How can i form a query for that..I need all the results in one row itself...Please help me in this.

[azimmer]

Hi azimmer,
Thanks for the query. It worked exactly the way i wanted..
I needed one more help..In the query we have only one condition to check if mode is 'Internet' alone..If i have one more condition to be checked like if mode is 'Internet' and Type is 'deposit' and another condition like mode is 'Internet' and type is 'Withdrawal'.. How can i form a query for that..I need all the results in one row itself...Please help me in this.

Rephrase the case and add one more column like this:

SELECT customerid, sum(Amnt) as TotalAmount,
sum(Amnt * (case when (Mode='Internet' and Type='deposit) then 1 else 0 end)) as InternetDeposits,
sum(Amnt * (case when (Mode='Internet' and Type='Withdrawal') then 1 else 0 end)) as InternetWithdrawals
FROM myTable
GROUP BY customerid

[anonymous]

Rephrase the case and add one more column like this:

SELECT customerid, sum(Amnt) as TotalAmount,
sum(Amnt * (case when (Mode='Internet' and Type='deposit) then 1 else 0 end)) as InternetDeposits,
sum(Amnt * (case when (Mode='Internet' and Type='Withdrawal') then 1 else 0 end)) as InternetWithdrawals
FROM myTable
GROUP BY customerid

Thank You so much...It worked...