1

I have two tables billcrown and bank_details. In billcrown there exist a unique row for each PJNO:

PJNO GRIRNO AMT
---- ------ ----
PJ1   GRIR1 1000
PJ2   GRIR2 150

Table bankdetails has 

PJNO GRIRNO AMT
---- ------ ---
PJ1  GRIR1  100
PJ2  GRIR2  150 
PJ1  GRIR1  200

I want to display all PJNO of the table billcrown where the sum of AMT of this PJNO in bankdetails is not equal to the AMT of this PJNO in billcrown

Here PJ1 in billcrown's amount is 1000 but the sum of amounts in bankdetails for the PJ1 is 300. So it should be displayed. But in the case of PJ2 the billcrown and bankdetails amount is the same.

Improve this question
1
  • Suppose in billcrowm table you have PJ3 also, do you want display this value ? Commented Jun 7, 2018 at 5:41

5 Answers 5

Reset to default
1

Something like this, perhaps?

select c.pjno
from billcrowm c join bankdetails d on d.pjno = c.pjno
group by c.pjno
having sum(c.amt) <> sum(d.amt);

[EDIT, after new information has been revealed]

Outer join with NVL might do the job.

select c.pjno
from billcrowm c left join bankdetails d on c.pjno = d.pjno
group by c.pjno
having sum(c.amt) <> nvl(sum(d.amt), 0);

[EDIT #2, after reading Sharad's comment]

with csum as 
  (select pjno, grirno, sum(amt) amt
   from billcrowm
   group by pjno, grirno
  ),
dsum as
  (select pjno, grirno, sum(amt) amt
   from bankdetails
   group by pjno, grirno
  )
select c.pjno
from csum c left join dsum d on c.pjno = d.pjno
group by c.pjno
having sum(c.amt) <> sum(nvl(d.amt, 0));
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks but the problem is there exist 2571 rows but it us showing only 65 rows.I checked the same and found that the remainig PJ's are not existing in bankdetails table.Kindly consider that case too,
Outer join with NVL; I edited my message, have a look.
tHANK YOU..YOU REALLY SAVE MY TIME
The solutions provided above doesn't work if Bankdetails table has one more row with value PJ1 GRIR1 700.
You're right, @Sharad, thank you. I edited my message and fixed it (hopefully).
1 
SELECT b.PJNO FROM billcrowm b
LEFT JOIN 
(
    SELECT PJNO, sum(amt) AS total_amt FROM bankdetails GROUP BY PJNO
)d
ON b.PJNO = d.PJNO
WHERE COALESCE(b.amt,0) <> COALESCE(d.total_amt,0);
Improve this answer

1 Comment

This solution works instead of the one provided above.
0 
IF OBJECT_ID('tempdb..#myTemp') IS NOT NULL
  DROP TABLE #myTemp

SELECT * INTO #myTemp FROM 
 (SELECT PJNO, SUM(AMT) AS Amount FROM 
 bankdetails 
 GROUP BY PJNO)
 AS tempResult

SELECT T.[PJNO], T.Amount, (T1.AMT - T.Amount) As AmountDiff
FROM #myTemp T
INNER JOIN  billcrowm  T1 ON T.[PJNO] = T1.[PJNO]
WHERE T.Amount <> T1.AMT
Improve this answer

Comments

0

This would work. 

with BankDetailsCTE 
as
(
Select PJNO, sum(AMT) as amt from BankDetails group by PJNO
)
Select d.Pjno from Billcrown d 
left join BankDetailsCTE c
on d.pjno = c.pjno
where d.AMT <> c.amt or c.amt is null
Improve this answer

Comments

0

If I understand well, all you need is a GROUP BY with an OUTER JOIN.

With these tables:

create table billcrown (PJNO, GRIRNO, AMT) as (
select 'PJ1', 'GRIR1', 1000 from dual union
select 'PJ2', 'GRIR2',  150 from dual union
select 'PJXX','XXXXX',  100 from dual
);
create table bankdetails (PJNO, GRIRNO, AMT) as (
select 'PJ1',  'GRIR1',  100 from dual union 
select 'PJ2',  'GRIR2',  150 from dual union  
select 'PJ1',  'GRIR1',  200 from dual
);

this

select bc.PJNO, bc.GRIRNO, bc.AMT, sum(bd.amt) 
from billCrown bc
        left join bankDetails bd
        on (bd.PJNO = bc.PJNO
            and bd.GRIRNO = bc.GRIRNO -- do you need this?
           )
group by bc.PJNO, bc.GRIRNO, bc.AMT
having sum(nvl(bd.amt, 0)) != bc.amt

gives:

PJNO GRIRNO        AMT SUM(BD.AMT)
---- ------ ---------- -----------
PJXX XXXXX         100            
PJ1  GRIR1        1000         300
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.