fresher fresher - 3 months ago 9
MySQL Question

subtracting count between two table

I am using MySQL

How I can count with subtracting record from another table.
For example, count = tab1 - tab2

Table: tab1

+-------------+----------------+
| studnetId + batchId +
+-------------+----------------+
+ 1 + 1 +
+-------------+----------------+
+ 2 + 1 +
+-------------+----------------+
+ 3 + 1 +
+-------------+----------------+
+ 4 + 1 +
+-------------+----------------+
+ 5 + 2 +
+-------------+----------------+
+ 6 + 2 +
+-------------+----------------+
+ 7 + 2 +
+-------------+----------------+


Table: tab2

+-------------+----------------+
| studnetId + batchId +
+-------------+----------------+
+ 1 + 1 +
+-------------+----------------+


Expected Result

+-------------+----------------+
| count + batchId +
+-------------+----------------+
+ 3 + 1 +
+-------------+----------------+
+ 2 + 2 +
+-------------+----------------+

Answer

Here's another approach using an outer join:

select t1.batchId, count(t1.studnetId) - count(t2.studnetId) as cnt
from t1 
       left join t2 on t1.batchId = t2.batchId and t1.studnetId = t2.studnetId 
group by t1.batchId

And I assume when batchId = 2, the count should be 3, not 2 as in your expected results.

Comments