sum of objects from same output

Question

I have some databases en joins in a query. What I'm hoping to have in the end is table "wanted_output" but could not get it to work.

The problem is this row :

SUM(CASE WHEN Orow.id_offer = Orow.id_offer THEN Orow.money_to_sum ELSE 0 END  ) AS sum_money

What i have this far is:

CREATE TABLE offer
    (id int,
    offer_name varchar(20),
    id_relation int);
    
INSERT INTO offer
    values(1, 12, 1), (2,9, 1),(3,13, 3);
    
CREATE TABLE relation
    (id int,
    name VARCHAR(20),
    id_group int);
    
INSERT INTO relation
    values(1, 'pietje', 1), (2,'klaasje', 1),(3,'Harry', 3);

CREATE TABLE offer_row
    (id int,
    name VARCHAR(20),
    id_offer int,
    money_to_sum int);
    
INSERT INTO offer_row
  values(1, 'some_row_1',1,12.50),(2, 'some_row_2',1, 22.34),(3, 'some_row_1',2, 44.50),(4, 'some_row_1',3, 99.90);
  
  
CREATE TABLE wanted_output
    (name VARCHAR(20),
    money_to_sum int,
    sum_money int);
    
INSERT INTO wanted_output
  values('pietje',12.50,79.34),('pietje',22.34,79.34),('pietje', 44.50,79.34),('Harry', 99.90 ,99.90);

        SELECT R.name , Orow.money_to_sum ,
        SUM(CASE WHEN Orow.id_offer = Orow.id_offer THEN Orow.money_to_sum ELSE 0 END  ) AS sum_money
        FROM offer O
        LEFT JOIN relation R ON R.id = O.id_relation
        LEFT JOIN offer_row Orow ON Orow.id_offer = O.id

sql fiddle

Answer 1

You are decribing a window sum:

select r.name, oi.money_to_sum, 
    sum(oi.money_to_sum) over(partition by r.id) as sum_moyen
from offer o
inner join relation r on r.id = o.id_relation
inner join offer_row oi on oi.id_offer = o.id

Demo on DB Fiddle - this works in SQL Server and in MySQL 8.0:

name   | money_to_sum | sum_moyen
:----- | -----------: | --------:
pietje |      12.5000 |   79.3400
pietje |      22.3400 |   79.3400
pietje |      44.5000 |   79.3400
Harry  |      99.9000 |   99.9000