Solved

# SQL query

Posted on 2010-01-04
284 Views
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
Question by:salmanfazal

LVL 39

Expert Comment

try this

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

Author Comment

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

LVL 39

Expert Comment

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

LVL 39

Accepted Solution

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

LVL 14

Expert Comment

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

0

LVL 14

Expert Comment

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

LVL 40

Expert Comment

try this.

0

LVL 40

Expert Comment

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 Closing Comment

Thnx
0

## Featured Post

### Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

#### Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!