Dwie sumy, dwóch tabel w jednym zapytaniu, Co robię źle? Opcje

[dawnthief]

Witajcie, jestem tu nowy i mam pewien problem.
Dane są dwie tabele o tej samej strukturze:

fm_expenses i fm_income
w expenses przechowywane są dane o wydatkach a w income o dochodach

ID int(11)
SUMA float(10,2)
DATA timestamp
CATEGORY int(3)
USERID int(11)


Chcę zliczyć sumę fm_income jako dochody a fm_expenses jako wydatki
robię to tak:

[SQL] pobierz, plaintext 
SELECT SUM(fm_expenses.SUMA) AS wydatki, SUM(fm_income.SUMA) AS dochody  FROM fm_expenses LEFT JOIN fm_income ON fm_expenses.USERID = fm_income.USERID WHERE fm_expenses.USERID=1 AND fm_income.USERID=1
[SQL] pobierz, plaintext 

i wychodzą bzdury. Kosmiczne cyfry, zdaję sobie sprawę że skaszaniona relacja tu jest, ale za to zwraca to wszystko w ładnym formacie (dwa pola, wydatki i dochody).
Wykombinowałem jescze jeden sposób:

[SQL] pobierz, plaintext 
SELECT SUM(SUMA) AS dochody FROM fm_income WHERE USERID=1 UNION SELECT SUM(SUMA) AS wydatki FROM fm_expenses WHERE USERID=1
[SQL] pobierz, plaintext 

Zwraca prawidłowe liczby ale wszystko w polu dochody i jako dwa rekordy.

Jak to ugryźć?

[mmmmmmm]

Tak to jest jak te dane się trzyma w dwóch różnych tabelach... Bez sensu.

[SQL] pobierz, plaintext 
SELECT w.USERID, dochody, wydatki FROM (SELECT USERID, Sum(SUMA) AS wydatki FROM fm_expenses GROUP BY USERID) w LEFT JOIN (SELECT USERID, Sum(SUMA) AS dochody FROM fm_income GROUP BY USERID) d ON d.USERID=w.USERID WHERE w.USERID=1
[SQL] pobierz, plaintext 

[trueblue]

mmmmmmm,
Twoje pytanie zwróci błędny wynik jeśli użytkownik będzie dochód bez wydatku.

[SQL] pobierz, plaintext 
SELECT userid,SUM(suma*(1-ABS(SIGN(typ-1)))) AS wydatek,SUM(suma*(1-ABS(SIGN(typ-2)))) AS dochod FROM
(SELECT w.USERID, w.SUMA, 1 AS typ FROM fm_expenses  AS w LEFT JOIN fm_income ON fm_income.USERID=w.USERID 
UNION ALL
SELECT d.USERID,d.SUMA,2 AS typ FROM fm_income AS d LEFT JOIN fm_expenses ON fm_expenses.USERID=d.USERID 
) AS tmp GROUP BY userid
[SQL] pobierz, plaintext 

Ten post edytował trueblue 6.10.2014, 17:01:52

[dawnthief]

Rozwiązanie które podał mmmmmmm podaje jak najbardziej prawidłowe wyniki, ma tylko jeden mankament, nie zwraca nic jeżeli dla danego usera (USERID) nie ma żadnego rekordu w wydatkach.
Rozwiązanie trueblue działa nawet jeśli nie ma rekordów w wydatkach lub dochodach ale niestety podaje nieprawidłowe sumy


SQL wyrzucił mi takie sumy , pierwsze to rozwiązanie mmmmmmm, drugie to rozwiązanie trueblue
wydatek dochod
2600.47 3316.95
1814.06999796629 1996.94998836517

[trueblue]

Podałem zapytanie dla wszystkich userów.
Spróbuj tak:

[SQL] pobierz, plaintext 
SELECT userid,SUM(suma*(1-ABS(SIGN(typ-1)))) AS wydatek,SUM(suma*(1-ABS(SIGN(typ-2)))) AS dochod FROM
(SELECT w.USERID, w.SUMA, 1 AS typ FROM fm_expenses  AS w LEFT JOIN fm_income ON fm_income.USERID=w.USERID WHERE w.userid=1
UNION ALL
SELECT d.USERID,d.SUMA,2 AS typ FROM fm_income AS d LEFT JOIN fm_expenses ON fm_expenses.USERID=d.USERID WHERE d.userid=1
) AS tmp
[SQL] pobierz, plaintext 

Ten post edytował trueblue 6.10.2014, 17:00:58

[dawnthief]

Wyrzuca błąd

[SQL] pobierz, plaintext 
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause 
[SQL] pobierz, plaintext 

a co robi te

[SQL] pobierz, plaintext 
suma*(1-ABS(SIGN(typ-1)))
[SQL] pobierz, plaintext 

?


Właśnie sobie uświadomiłem jak mało wiem o SQLu...

[trueblue]

Dodaj na końcu całości GROUP BY userid.

[SQL] pobierz, plaintext 
suma*(1-ABS(SIGN(typ-1)))
[SQL] pobierz, plaintext 

Umożliwia emulację tabeli przestawnej.

[dawnthief]

Tak teraz błędem nie rzuca, ale wciąż podaje niewłaściwe sumy.

[trueblue]

Na 100% jesteś pewien, że wynik nie jest poprawny?
Ja testuję na, co prawda, paru rekordach, ale jest ok.

EDIT: Powinno być UNION ALL, choć nie wiem czy to jest przyczyną wyniku, który uznajesz za błędny.

Ten post edytował trueblue 6.10.2014, 17:04:18

[dawnthief]

Teraz już kompletnie odwaliło

[SQL] pobierz, plaintext 
    SELECT userid,SUM(suma*(1-ABS(SIGN(typ-1)))) AS wydatek,SUM(suma*(1-ABS(SIGN(typ-2)))) AS dochod FROM
    (SELECT w.USERID, w.SUMA, 1 AS typ FROM fm_expenses AS w LEFT JOIN fm_income ON fm_income.USERID=w.USERID WHERE w.userid=2
    UNION ALL
    SELECT d.USERID,d.SUMA,2 AS typ FROM fm_income AS d LEFT JOIN fm_expenses ON fm_expenses.USERID=d.USERID WHERE d.userid=2
    ) AS tmp GROUP BY USERID
[SQL] pobierz, plaintext 

Wrzucam takie zapytanie (zmieniłem userid z 1 na 2 , bo chciałem sprawdzić na małej ilości danych)
mam w dochodach dwa rekordy SUMA=3.00 co powinno dawać 6
a w wydatkach jeden rekord SUMA=2.00 a drugi SUMA=2.50 co powinno dawać 4.50

Otrzymuję taki wynik:
wydatek: 9
dochod: 12

[trueblue]

Niepotrzebne te LEFT JOIN.

Dla USERID=1

[SQL] pobierz, plaintext 
SELECT userid,SUM(suma*(1-ABS(SIGN(typ-1)))) AS wydatek,SUM(suma*(1-ABS(SIGN(typ-2)))) AS dochod FROM
(SELECT USERID,SUMA, 1 AS typ FROM fm_expenses
UNION ALL
SELECT USERID,SUMA,2 AS typ FROM fm_income
) AS tmp WHERE userid=1 GROUP BY userid
[SQL] pobierz, plaintext 

[dawnthief]

Działa! wszystko cacy, punkcik powędrował.
Teraz jescze pare pytań żebym bezmyślnie nie kopiował i wklejał

[SQL] pobierz, plaintext 
    SELECT userid,SUM(suma*(1-ABS(SIGN(typ-1)))) AS wydatek,SUM(suma*(1-ABS(SIGN(typ-2)))) AS dochod FROM
    (SELECT USERID,SUMA, 1 AS typ FROM fm_expenses
    UNION ALL
    SELECT USERID,SUMA,2 AS typ FROM fm_income
    ) AS tmp WHERE userid=1 GROUP BY userid
[SQL] pobierz, plaintext 

- Skąd się bierze te 1 i 2 w tych Selectach po sumie ?
- SUM(suma*(1-ABS(SIGN(typ-1)))) - jak to dokładnie działa, po co i dlaczego?

[trueblue]

Wydatek ma typ=1, dochód=2.

Dla typ=1 jeśli rekord ma typ=1, będzie suma*1, inaczej suma*0, analogicznie dla typ=2.
Można to zastąpić taką konstrukcją:

[SQL] pobierz, plaintext 
SUM(suma*(IF(typ=1,1,0))) AS wydatek,SUM(suma*(IF(typ=2,1,0)))
[SQL] pobierz, plaintext 

Krótko: wartość suma wpada do sumowania wydatku jeśli jest wydatkiem, jeśli nie jest, nie wpada. Analogicznie dla dochodu.