How to get percentage from Total SUM SQL

Question

I have 3 tables to calculate payments, received and paid:

1. Company

idcompany	company
1	APPLE
2	GOOGLE
3	MICROSOFT
4	STEAM
5	AMAZON
6	LG

2. Pay:

id	company	dueday	valuep
1	APPLE	03/10/2022	200,00
2	APPLE	04/10/2022	600,00
3	GOOGLE	04/10/2022	600,00
4	MICROSOFT	04/10/2022	500,00
5	MICROSOFT	04/10/2022	60,00
6	APPLE	04/10/2022	100,00

3.Receive:

idconta	company	issuedate	valuer
104	MICROSOFT	03/10/2022	70000,00
106	STEAM	03/10/2022	15000,00
107	STEAM	03/10/2022	42000,00
108	AMAZON	03/10/2022	46000,00
109	LG	03/10/2022	3200,00

I tried this query to make a report with amount paid and received during a certain period and the percentage of value received/total value.

I need a report with amount paid and received during a certain period and percentage of value received under total amount:

idconta	company	issuedate	valuer	percentage
104	MICROSOFT	03/10/2022	70000,00	39% (70000/176200)
106	STEAM	03/10/2022	15000,00	8% (15/176200)
107	STEAM	03/10/2022	42000,00	23% (42000/176200)
108	AMAZON	03/10/2022	46000,00	26% (46000/176200)
109	LG	03/10/2022	3200,00	1% (32000/176200)
Total			176200,00	-------------------

I tried SUM(valuer) * 100.0 / SUM(SUM(valuer)) OVER () AS Percentage as suggested in Percentage from Total SUM after GROUP BY SQL Server but did not work. Query that is not working below and fiddle https://dbfiddle.uk/JHHomMi3

SELECT o.company, value_pay, value_receive 
FROM app_company AS o 
LEFT JOIN  (select sum(valuep) as value_pay,company from app_pay 
  where date(dueday) BETWEEN '2022-10-01' AND '2022-10-30' group by company) 
  AS n ON o.company=n.company 
LEFT JOIN  (SELECT SUM(valuer)  AS value_receive,  
  SUM(valuer) * 100.0 / SUM(SUM(valuer)) OVER () AS Percentage, 
  company from app_receive
 where date(issuedate) BETWEEN '2022-10-01' AND '2022-10-30'group by company)
  AS r ON o.company=r.company order by o.company asc;

Answer 1

You've got some table unused in your example, but SUM … OVER() is indeed the way to go:

SELECT 
  c.company, 
  CONCAT(
    ROUND(
      SUM(IFNULL(r.valuer, 0)) * 100.0 / SUM(r.valuer) OVER (), 2), 
    '%') AS percent
FROM app_company c
LEFT JOIN app_receive r ON c.company = r.company
GROUP BY c.company, r.valuer

-- (company)    (percent)
-- APPLE        0%
-- GOOGLE       0%
-- MICROSOFT    39.73%
-- STEAM        23.84%
-- STEAM        8.51%
-- AMAZON       26.11%
-- LG           1.82%

Dataset used:

CREATE TABLE app_company (
  idcompany INTEGER PRIMARY KEY,
  company TEXT NOT NULL
);
INSERT INTO app_company VALUES
(1, 'APPLE'),
(2, 'GOOGLE'),
(3, 'MICROSOFT'),
(4, 'STEAM'),
(5, 'AMAZON'),
(6, 'LG');

CREATE TABLE app_receive (
  idconta INTEGER PRIMARY KEY,
  company TEXT NOT NULL,
  issuedate DATE NOT NULL,
  valuer FLOAT NOT NULL
);
INSERT INTO app_receive VALUES 
(104,   'MICROSOFT',    '2022-10-03',   70000.00),
(106,   'STEAM',        '2022-10-03',   15000.00),
(107,   'STEAM',        '2022-10-03',   42000.00),
(108,   'AMAZON',       '2022-10-03',   46000.00),
(109,   'LG',           '2022-10-03',   3200.00);