MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE products (
  `productid` INTEGER,
  `productname` VARCHAR(4),
  `customerid1` INTEGER,
  `qty` INTEGER
);

INSERT INTO products
  (`productid`, `productname`, `customerid1`, `qty`)
VALUES
  ('3', 'car', '1', '5'),
  ('3', 'car', '2', '5'),
  ('1', 'boat', '3', '1'),
  ('1', 'boat', '4', '2'),
  ('3', 'car', '1', '5');

Records: 5  Duplicates: 0  Warnings: 0


CREATE TABLE customer (
  `customerid2` INTEGER,
  `name` VARCHAR(5),
  `date` INTEGER
);

INSERT INTO customer
  (`customerid2`, `name`, `date`)
VALUES
  ('1', 'jon', '2020'),
  ('2', 'steve', '2020'),
  ('3', 'ted', '2020'),
  ('4', 'ned', '2020');

Records: 4  Duplicates: 0  Warnings: 0


SELECT 
  c.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sum
FROM customer c inner JOIN products p ON c.customerid2 = p.customerid1
inner Join (SELECT productid, SUM(qty) total_sum FROM products GROUP BY productid) p1 
  ON p.productid = p1.productid 
WHERE `date` BETWEEN '2019' AND '2020'
GROUP by c.customerid2, c.name,p.productid,p.productname
ORDER by total DESC;


customerid2
name
productname
total
total_sum


1
jon
car
10
15

2
steve
car
5
15

4
ned
boat
2
3

3
ted
boat
1
3


SELECT json_object('product', CONCAT( total_sum,' ',productname), 'customer', 
json_object('customerid', customerid2, 'name', name,'total', total)) json_result
FROM
(SELECT 
  c.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sum
FROM customer c inner JOIN products p ON c.customerid2 = p.customerid1
inner Join (SELECT productid, SUM(qty) total_sum FROM products GROUP BY productid) p1 
  ON p.productid = p1.productid 
WHERE `date` BETWEEN '2019' AND '2020'
GROUP by c.customerid2, c.name,p.productid,p.productname
ORDER by total DESC) t2


json_result


{"product": "15 car", "customer": {"name": "jon", "total": 10, "customerid": 1}}

{"product": "15 car", "customer": {"name": "steve", "total": 5, "customerid": 2}}

{"product": "3 boat", "customer": {"name": "ned", "total": 2, "customerid": 4}}

{"product": "3 boat", "customer": {"name": "ted", "total": 1, "customerid": 3}}


SELECT
    json_object('product', CONCAT( total_sum,' ',productname), 'customer',json_result) json_result
FROM
	(SELECT  
		productname
		,totaL_SUM
		,JSON_ARRAYAGG(json_object('customerid', customerid2, 'name', name,'total', total)) json_result
	FROM
		(SELECT 
		  c.customerid2, c.name,p.productname,SUM(qty) total, p1.total_sum
		FROM customer c inner JOIN products p ON c.customerid2 = p.customerid1
		inner Join (SELECT productid, SUM(qty) total_sum FROM products GROUP BY productid) p1 
		  ON p.productid = p1.productid 
		WHERE `date` BETWEEN '2019' AND '2020'
		GROUP by c.customerid2, c.name,p.productid,p.productname
		ORDER by total DESC) t2
	GROUP BY productname,total_sum) t3


json_result


{"product": "3 boat", "customer": [{"name": "ned", "total": 2, "customerid": 4}, {"name": "ted", "total": 1, "customerid": 3}]}

{"product": "15 car", "customer": [{"name": "jon", "total": 10, "customerid": 1}, {"name": "steve", "total": 5, "customerid": 2}]}