CODEWITHSUNDEEP
sql-server
azz845
azz845

Reputation: 33

how to subtract records coming from one query from the records that is coming from another query?

these are the two queries, returning multiple records, so is there any solution to subtract each row data of one result from another result?

query1:

SELECT COUNT(*) As AbhidayNotPaidSansthan
 FROM PanjikaranMaster GROUP BY Zila

query2:

SELECT COUNT(*) as anps FROM AbhidayMaster,PanjikaranMaster
    WHERE PanjikaranMaster.OfficeRegId=AbhidayMaster.OfficeRegId AND
    AbhidayMaster.DipositDate Between ('2012-09-19') AND ('2012-09-24')
GROUP BY PanjikaranMaster.Zila

Upvotes: 2

Views: 1320

Answers (1)

Akash KC
Akash KC

Reputation: 16310

Try with this query, it will solve your problem:

SELECT ISNULL(a.cnt,0) -  ISNULL(b.cnt,0) AS CNT,a.Zila
FROM
    (SELECT Zila,COUNT(*) as cnt As AbhidayNotPaidSansthan
    FROM PanjikaranMaster GROUP BY Zila) a
    LEFT JOIN 
    (SELECT Zila,COUNT(*) as cnt FROM AbhidayMaster,PanjikaranMaster
    WHERE PanjikaranMaster.OfficeRegId=AbhidayMaster.OfficeRegId AND
    AbhidayMaster.DipositDate Between ('2012-09-19') AND ('2012-09-24')
    GROUP BY PanjikaranMaster.Zila) b 
    ON a.Zila = b.Zila

I have done similar test in SQLFIDDLE

Upvotes: 2

Related Questions