By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE businesses_stats (
id INT AUTO_INCREMENT PRIMARY KEY,
Biz_id CHAR(1),
Biz_Date DATE,
Biz_Revenue INT
);
ALTER TABLE businesses_stats
ADD UNIQUE INDEX ((CASE WHEN Biz_id = '1' THEN 1 END), Biz_Date);
INSERT INTO businesses_stats (Biz_id, Biz_Date, Biz_Revenue)
VALUES ('1', '2022-02-02', 1)
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue + VALUES(Biz_Revenue);
INSERT INTO businesses_stats (Biz_id, Biz_Date, Biz_Revenue)
VALUES ('2', '2022-02-02', 10)
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue + VALUES(Biz_Revenue);
INSERT INTO businesses_stats (Biz_id, Biz_Date, Biz_Revenue)
VALUES ('1', '2022-02-02', 100)
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue + VALUES(Biz_Revenue);
INSERT INTO businesses_stats (Biz_id, Biz_Date, Biz_Revenue)
VALUES ('2', '2022-02-02', 1000)
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue + VALUES(Biz_Revenue);
INSERT INTO businesses_stats (Biz_id, Biz_Date, Biz_Revenue)
VALUES ('1', '2022-02-03', 1)
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue + VALUES(Biz_Revenue);
INSERT INTO businesses_stats (Biz_id, Biz_Date, Biz_Revenue)
VALUES ('2', '2022-02-03', 10)
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue + VALUES(Biz_Revenue);
INSERT INTO businesses_stats (Biz_id, Biz_Date, Biz_Revenue)
VALUES ('1', '2022-02-03', 100)
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue + VALUES(Biz_Revenue);
INSERT INTO businesses_stats (Biz_id, Biz_Date, Biz_Revenue)
VALUES ('1', '2022-02-03', 1000)
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue + VALUES(Biz_Revenue);
INSERT INTO businesses_stats (Biz_id, Biz_Date, Biz_Revenue)
VALUES ('2', '2022-02-03', 10000)
ON DUPLICATE KEY UPDATE Biz_Revenue = Biz_Revenue + VALUES(Biz_Revenue);
SELECT * FROM businesses_stats;
| id | Biz_id | Biz_Date | Biz_Revenue |
|---|---|---|---|
| 1 | 1 | 2022-02-02 | 101 |
| 2 | 2 | 2022-02-02 | 10 |
| 4 | 2 | 2022-02-02 | 1000 |
| 5 | 1 | 2022-02-03 | 1101 |
| 6 | 2 | 2022-02-03 | 10 |
| 9 | 2 | 2022-02-03 | 10000 |