CODEWITHSUNDEEP
mysqlsql
Osama Gadour
Osama Gadour

Reputation: 187

SQL query to sum two different values of same ID column in one query

kindly, can someone help me with this, I have the following table:

Table Name : balances

cname       amount      type
--------------------------------------
Jhon        150     A
Jhon        200     B
Jhon        100     A
Jhon        30      A
Jhon        55      B

======================================

I want an SQLquery gives me this result:

cname       totalOf_A   totalOf_B  
---------------------------------------  
Jhon        280           255  
=========================================

I've tried the follwoing:

select ,cname,sum(amount),type from balances group by cname,type

the result:

cname      amount    type  
----       -----     ----  
Jhon        250       A  
Jhon        255       B

so, I want the result in one row and in one query please.
Any suggestions please.
Thanks in advance.

Upvotes: 1

Views: 2760

Answers (3)

LONG
LONG

Reputation: 4620

 select A.cname,A.typeof_A,B.typeof_B
  FROM
  (
  select cname,sum(amount) as typeof_A
   from balances 
   where type = 'A'
   group by cname
   ) as A

   INNER JOIN

   (
  select cname,sum(amount) as typeof_B
   from balances 
   where type = 'B'
   group by cname
   ) as B
  ON B.cname = A.cname

Upvotes: 0

sschmitz
sschmitz

Reputation: 442

It sounds like you are going to want to use a PIVOT. There are some examples in the docs.

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

What you're trying to do is called conditional aggregation. You can use

select 
cname,
sum(case when type='A' then amount else 0 end) as total_A,
sum(case when type='B' then amount else 0 end) as total_B 
from balances 
group by cname

Upvotes: 3

Related Questions