Sum value is too big when adding a another table ORACLE SQL

select NVL((select name from supplier where id = t.id_supplier), 'All suppliers') as suppliers,  
        NVL((select name from stadium where id = m.id_stadium), 'All stadium') as stadium,   
        NVL((select name from league where id = m.id_league), 'All league') as league, 
        (NVL(sum(b.price), 0) + sum(t.cost)) as incomes
FROM match m
inner join ticket b on m.id = b.id_match
inner join stadion s on s.id = m.id_stadium
inner join league l on l.id = m.id_league
inner join transmission t on t.id = m.id_transmission
inner join supplier d on d.id = t.id_supplier
group by cube(d.id, s.id, l.id);

When I want to sum two values from two different tables – the tickets sold out on match and the TV transmission i got too big sum value in result. I figured out that for single match transmission cost is X times too big, where X is a number of tickets for this match, only when I join a tickets table. How can I repair this?

I found solution for similiar problem here: SQL: After joining tables SUM() function returns wrong value but it doesn’t work with cube.

For example sum of bilets for match of id 1 is 410.5:

select sum(b.price) from match m
inner join ticket b on m.id = b.id_match
where m.id = 1;

is giving result 410.5 so correct. The sum for transmission is 300 000:

select sum(t.cost) from match m
inner join transmission t on m.id_transmission = t.id
where m.id = 1;

and it also return correct value. But when I add a ticket table I got 1200410,5 value what is incorrect:

select sum(t.cost) + sum(b.price) from match m
inner join transmission t on m.id_transmission = t.id
inner join ticket b on b.id_match= m.id
where m.id = 1;

the correct value should be 300410.5.

Answer

After reading your question, the following query is giving result 410.5 so correct, but the count is returning 4 (number of tickets for this match)

   select sum(b.price), count(*) as cnt from match m
   inner join ticket b on m.id = b.id_match
   where m.id = 1;

the second query is returning 300 000 and the count is returning 1 (only one transmission)

select sum(t.cost), count(*) cnt from match m
inner join transmission t on m.id_transmission = t.id
where m.id = 1;

When you are joining the 3 tables, you have now 4 rows for the last query (transmissions) and not only 1 as expected. This is why you figured out that for single match transmission cost is X times too big, where X is a number of tickets for this match. Run the following query to validate

select m.id_transmission, cost  from match m
inner join transmission t on m.id_transmission = t.id
inner join ticket b on b.id_match= m.id
where m.id = 1;

ID_TRANSMISSION	COST
5	300000
5	300000
5	300000
5	300000

So, the solution is join the aggregations query AND not the single tables. Below an example using the SQL WITH syntax to build the subquery

with agg_ticket as (select id_match, sum(b.price) as price from ticket b group by id_match) 
select sum(t.cost) + sum(b.price)  from match m
inner join transmission t on m.id_transmission = t.id
inner join agg_ticket  b on b.id_match= m.id
where m.id = 1;

If the count for the transmission query returns more than 1 (count >1), you need create also a subquery

with agg_ticket as (select id_match, sum(b.price) as price from ticket b group by id_match) ,
     agg_transmission as (select id, sum(t.cost) as cost from transmission t group by id)
select sum(t.cost) + sum(b.price)  from match m
inner join agg_transmission t on m.id_transmission = t.id
inner join agg_ticket  b on b.id_match= m.id
where m.id = 1;

This returns the correct value 300410.5

Then, you can add the cube syntax, I have not all tables and data to test

with agg_ticket as (select id_match, sum(b.price) as price from ticket b group by id_match) ,
     agg_transmission as (select id, sum(t.cost) as cost from transmission t group by id)
select nvl(CAST (m.id AS VARCHAR2(2000)), 'ALL MATCHS') as idMatch,
m.id,
sum(t.cost) + sum(b.price)  from match m
inner join agg_transmission t on m.id_transmission = t.id
inner join agg_ticket  b on b.id_match= m.id
group by cube (m.id);

Hope that it can help

By the way, if you have the following error : ORA-00979: not a GROUP BY expression when running this query

select NVL((select name from supplier where id = t.id_supplier), 'All suppliers') as suppliers,  
        NVL((select name from stadium where id = m.id_stadium), 'All stadium') as stadium,   
        NVL((select name from league where id = m.id_league), 'All league') as league, 
        (NVL(sum(b.price), 0) + sum(t.cost)) as incomes
FROM match m
inner join ticket b on m.id = b.id_match
inner join stadion s on s.id = m.id_stadium
inner join league l on l.id = m.id_league
inner join transmission t on t.id = m.id_transmission
inner join supplier d on d.id = t.id_supplier
group by cube(d.id, s.id, l.id);

this is because, you must to use the same columns used in group by cube in the SELECT. Because you are doing NVL with a SELECT expression, take care with the WHERE clause inside the NVL expression

NVL((select name from league where id = l.id), 'All league') as league

and not

NVL((select name from league where id = m.id_league), 'All league') as league

Even if l.id = m.id_league You are doing group by cube(d.id, s.id, l.id);

In your case, I suggest to modify the GROUP BY and keep the SELECT :

group by cube(t.id_supplier, m.id_stadium, m.id_league);