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.
Advertisement
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);