Podsumowanie przychodów i kosztów dla projektu

Witam,

próbuję napisać funkcję, która łączy 3 tabele.

Pierwsza tabela zawiera id projektu i nazwę
Druga id projketu oraz koszt,
Trzecia id projektu oraz przychód.

W tabeli przychodów i kosztów może być kilka rekordów dla jednego projektu.

W jaki sposób mogę w prosty sposób napisać podsumowanie przychodów i kosztów dla projektu

Jeżeli próbuję zrobić join 3 tabel to funkcja agregująca zlicza mi kilkukrotnie wartość (tyle ile jest rekordów w 3 tabeli).

W sumie rozwiązałem to w ten sposób:

select projekt.id, first(przychody.wartosc), sum(koszty.wartosc)
from projekty
left join porzychody on projekty.id = przychody.id
left join koszty on projekty.id = koszty.id
group by projekty.id

W tym przypadku jest OK, ale wydaje mi się, że można to napisać lepiej.

Jeśli przychodów i kosztów może być wiele (albo wcale) to zamień first na sum

select 
  projekt.id, 
  sum(przychody.wartosc),
  sum(koszty.wartosc)
from 
  projekty
  left  join porzychody on projekty.id = przychody.id
  left  join koszty on projekty.id = koszty.id
group 
  by projekty.id

grzegorz_so napisał(a):

Jeśli przychodów i kosztów może być wiele (albo wcale) to zamień first na sum

select 
  projekt.id, 
  sum(przychody.wartosc),
  sum(koszty.wartosc)
from 
  projekty
  left  join porzychody on projekty.id = przychody.id
  left  join koszty on projekty.id = koszty.id
group 
  by projekty.id

No ale to właśnie to nie działa. Po joinie z tablicą przychody tworzony jest następnie iloczyn kartezjański z tablicą kosztów. Następnie suma jest liczona dla iloczynu kartezjańskiego (czyli wynik jest tyle razy większy ile mamy rekordów w talicy kosztów).
Tabela projekty

id wartosc
1 projekt1
2 projekt2
3 projekt3

Tabela przychody
id wartosc
2 100
2 300
3 300
3 400

Tabela koszty
id wartosc
2 100
1 100
3 150
3 151

Wynik zapytania
1 NULL 100
2 400 200
3 1400 602

Wartość przychodów jest dwa razy za duża dla projektu trzeciego. Powinno być 700, a nie 1400. Jest zwiększony dwa razy ponieważ drugi join jest

1 NULL 100
2 400 200
3 300 150
3 300 151
3 400 150
3 400 151

No i w tym momencie funkcja agregacyjna zwraca to co zwraca. Nie to co bym oczekiwał.

W poprzednim poście zasugerowałem się grupowaniem w Twoim zapytaniu.
Spróbuj tego :

select 
  projekt.id, 
  (select sum(przychody.wartosc) from przychody where projekty.id = przychody.id) as przychod,
  (select sum(koszty.wartosc) from koszty where projekty.id = koszty.id) as koszt 
from 
  projekty

grzegorz_so napisał(a):

W poprzednim poście zasugerowałem się grupowaniem w Twoim zapytaniu.
Spróbuj tego :

select 
  projekt.id, 
  (select sum(przychody.wartosc) from przychody where projekty.id = przychody.id) as przychod,
  (select sum(koszty.wartosc) from koszty where projekty.id = koszty.id) as koszt 
from 
  projekty

Wynik Twojego zapytania jest jak najbardziej OK. Dziękuję.

Panie, to zapytanie zabije ci bazę przy dużej ilości danych.
Już lepsze jest:

SELECT p.id, p.wartosc, pr.przychody, ko.koszty
FROM
  projekty p
  LEFT JOIN
  (SELECT id, sum(wartosc) przychody FROM przychody GROUP BY id) pr
  ON p.id=pr.id
  LEFT JOIN
  (SELECT id, sum(wartosc) koszty FROM koszty GROUP BY id) ko
  ON p.id=ko.id  

A najlepsze by było na CTE (ale nie chce mi się pisać)

@Marcin.Miga:

Zgoda :)
Przy dużej ilości danych (liczbie projektów) może być problem z wydajnością.
Ale jeśli do ''query'' dodamy warunek ''where" mocno zawężający wynikowy zbiór projektów, to lepsza wydajność Twojego rozwiązania już nie jest tak oczywista.
Fakt, w temacie postu nie ma ani słowa o ograniczeniu wyniku zapytania do jakiegoś podzbioru projektów