CREATE TABLE `A` ( `Id` int(11) default NULL, `Count` int(11) default NULL ) ENGINE=InnoDB; INSERT INTO `A` VALUES (1,5),(2,5); CREATE TABLE `B` ( `Id` int(11) default NULL ) ENGINE=InnoDB; INSERT INTO `B` VALUES (1),(1),(2),(1); SELECT A.*, @Sum:=SUM(A.Count) Sum, @Count:=(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) Count, SUM(A.Count)-(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) X FROM A GROUP BY A.Id; SELECT A.*, @Sum:=SUM(A.Count) Sum, @Count:=(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) Count, @Sum-@Count X FROM A GROUP BY A.Id; SELECT A.*, @Sum:=SUM(A.Count) Sum, @Count:=(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) Count, SUM(A.Count)-@Count X FROM A GROUP BY A.Id; SELECT A.*, @Sum:=SUM(A.Count) Sum, @Count:=(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) Count, @Sum-(SELECT COUNT(B.Id) FROM B WHERE B.Id=A.Id) X FROM A GROUP BY A.Id; SELECT A.*, IFNULL(B.X, 0) Count, SUM(A.Count)-IFNULL(B.X, 0) X FROM A LEFT JOIN ( SELECT B.*, COUNT(B.Id) X FROM B GROUP BY B.Id ) B USING (Id) GROUP BY A.Id;