• Status: Solved
• Priority: Medium
• Security: Public
• Views: 289

# SQL query

I am writting a SQL query. Below is the sample table. The result i want is for each customer, difference of sum of by and sum of sell.

Table:

customer     trtype      amount
0001            by           10000
0001            by            2000
0001            by            500
0001            sl             3000
0001            sl             2000
0002            by           60000
0002            by            4000
0002            by            500
0002            sl             3000
0002            sl             2000

example:
0001 :   (10000+2000+500) - (3000+2000) = 12500-5000 = 7500
0002 :   (60000+4000+500)-(3000+2000) =  64500-5000 = 59500

Result should be

0001 - 7500
0002 -  59500
0
salmanfazal
• 3
• 2
• 2
• +1
1 Solution

Commented:
try this

Select customer, sum(case when trtype='by' then amount else -amount end)
from tablename
group by customer
0

Author Commented:
can you show me the above with an inner join with customer table to pickup the customername also from master table.

table could be

customer:

Code      name
0001      ABC
0002      EFG

result would be :

ABC - 7500
EFG -  59500
0

Commented:
try this

Select A.customer, , B.customerName,  sum(case when trtype='by' then amount else -amount end)
from tablename A join customerTable B
on A.customer = b.Customer
group by A.customer, B.customerName
0

Commented:
try this

Select A.customer, B.Name,  sum(case when trtype='by' then amount else -amount end)
from tablename A join customerTable B
on A.customer = b.code
group by A.customer, B.Name
0

Commented:
select customer, sum(decode(trtype ,'by',amount,0) - decode(trtype ,'sl',amount,0)) bal  from t1
group by  customer

0

Commented:
select customer, sum(decode(trtype ,'by',amount,0) - decode(trtype ,'sl',amount,0)) bal  from t1 inner join  cust on (t1.cust_code = cust.cust_code)
group by  customer

ry this
0

Data EngineerCommented:
try this.

0

Data EngineerCommented:
missed the query.

select c.name,by_amount - sl_amount as final_amount
from (select customer,
sum(case when trtype = 'by' then amount else 0 end) as by_amount,
sum(case when trtype = 'sl' then amount else 0 end) as sl_amount
from SampleTable
group by customer) as t1
join customer c on t1.customer = c.code
0

Author Commented:
Thnx
0

## Featured Post

• 3
• 2
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.