MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.7.39


CREATE TABLE `ac` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `d_qty` double(11,2) DEFAULT NULL,
  KEY `id` (`id`)
)
CREATE TABLE `aa` (
    `id` INT NOT NULL AUTO_INCREMENT,
  `cust_Id` INT,
  `advance` INT,
  `date` DATE,
   KEY(`id`)
);
CREATE TABLE `as` (
    `id` INT NOT NULL AUTO_INCREMENT,
   `tid` INT,
  `cust_Id` INT,
  `date` DATE,
  `typ` INT,
  `qty` double(11,2),
   KEY(`id`)
);
Insert into ac (name, d_qty) VALUES
("a", 0.25),
("b", 0.5),
("c", 0.75),
("d", 1),
("e", 0.25);

Records: 5  Duplicates: 0  Warnings: 0


select * from ac;


id
name
phone
d_qty


1
a
null
0.25

2
b
null
0.50

3
c
null
0.75

4
d
null
1.00

5
e
null
0.25


INSERT INTO aa (`cust_Id`, advance, DATE) VALUES
(1, 500, "2022-3-31"),
(2, 500, "2022-3-31"),
(3, 250, "2022-3-31");

Records: 3  Duplicates: 0  Warnings: 0


select * from aa;


id
cust_Id
advance
date


1
1
500
2022-03-31

2
2
500
2022-03-31

3
3
250
2022-03-31


INSERT INTO `as` ( tid, `cust_Id`, DATE, typ, qty ) VALUES
(8, 2, "2022-3-31", 0, 0.25),
(8, 2, "2022-3-31", 0, 0.25),
(8, 1, "2022-3-31", 0, 0.25),
(8, 3, "2022-3-31", 0, 0.75);

Records: 4  Duplicates: 0  Warnings: 0


select * from `as`;


id
tid
cust_Id
date
typ
qty


1
8
2
2022-03-31
0
0.25

2
8
2
2022-03-31
0
0.25

3
8
1
2022-03-31
0
0.25

4
8
3
2022-03-31
0
0.75


SELECT custID, advance, sales FROM (
SELECT id AS custID FROM ac) c
JOIN (
SELECT SUM(advance) AS advance, cust_id FROM aa GROUP BY cust_id) a ON c.custID = a.cust_id 
JOIN (
SELECT SUM(qty) AS sales, cust_id FROM `as` GROUP BY cust_id) s ON c.custID = s.cust_id ;


custID
advance
sales


1
500
0.25

2
500
0.50

3
250
0.75

id	name	phone	d_qty
1	a	null	0.25
2	b	null	0.50
3	c	null	0.75
4	d	null	1.00
5	e	null	0.25

id	tid	cust_Id	date	qty
1	8	2	2022-03-31	0.25
2	8	2	2022-03-31	0.25
3	8	1	2022-03-31	0.25
4	8	3	2022-03-31	0.75