CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Manoj Patil
Manoj Patil

Reputation: 65

Sql Query to Compare Same Field having differnet values because of Group Clause

I Have Query where I need to compare a amount field which has grouped by debit credit, I want to get the out put where the amount of credit is not equal to amount of debit the query is

select t_vocno,
       sum(t_amt),
       dc_type 
from   accotran 
where  f_yr = '1718' 
and    comp_cd = 'skl' 
group by  t_vocno, 
          dc_type 
order by t_vocno

which gives output 

1   215452.1600 D
1   215452.1600 C
2   207586.0000 D
2   207586.0000 C
3   248789.0000 D
3   248789.0000 C

I have very bid data so I want to put a having condition and get the data where debit <> credit

I have tried

select t_vocno,
       sum(t_amt),
       dc_type 
from   accotran 
where  f_yr = '1718' 
and    comp_cd = 'skl' 
group by t_vocno,
         dc_type 
having case when dc_type= 'c' and t_vocno = t_vocno then sum(t_amt) end <>
       case when dc_type= 'd' and t_vocno = t_vocno then sum(t_amt)  end
order by t_vocno

Upvotes: 0

Views: 38

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can GROUP BY just t_vocno and use conditional aggregation to calculate credit / debit sums:

select t_vocno,
       sum(case when dc_type= 'c' then t_amt else 0 end) as c_sum,
       sum(case when dc_type= 'd' then t_amt else 0 end) as d_sum
from   accotran 
where  f_yr = '1718' 
and    comp_cd = 'skl' 
group by t_vocno
having sum(case when dc_type= 'c' then t_amt else 0 end) <>
       sum(case when dc_type= 'd' then t_amt else 0 end)
order by t_vocno

Upvotes: 3

Related Questions