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

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
Asked:
salmanfazal
  • 3
  • 2
  • 2
  • +1
1 Solution
 
appariCommented:
try this

Select customer, sum(case when trtype='by' then amount else -amount end)
from tablename
group by customer
0
 
salmanfazalAuthor 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
 
appariCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
appariCommented:
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
 
shru_0409Commented:
select customer, sum(decode(trtype ,'by',amount,0) - decode(trtype ,'sl',amount,0)) bal  from t1
group by  customer

0
 
shru_0409Commented:
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
 
SharathData EngineerCommented:
try this.

0
 
SharathData 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
 
salmanfazalAuthor Commented:
Thnx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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