MySQL SUM() - 2 Zwischensummen errechnen, 1 Endsumme ausgeben, nach Endsumme sortieren

[David93]

Mahlzeit liebe Kollegen :=)

Ich habe derzeit ein kleines Problem mit einer MySQL Abfrage, welches mich mittlerweile zum verzweifeln bringt.

Und zwar möchte ich mir aus einer Datenbanktabelle 2 seperate SUM()s ausrechnen lassen, diese dann voneinander Subtrahieren, und nach den endgültigen höchsten Zahlen sortieren.
Ich hab zwar schon mehrere Methoden versucht, komme aber zu keiner Lösung .. :/

1.) Versuch
SELECT SUM(p.account_points_type) AS plus, SUM(m.account_points_type) AS minus, SUM(plus - minus) AS count, u.user_name, u.user_status
FROM tabelle_users AS u
LEFT JOIN tabelle_accounts AS p ON (u.user_id=p.account_owner AND p.account_points_type='1')
LEFT JOIN tabelle_accounts AS m ON (u.user_id=m.account_owner AND m.account_points_type='0')
ORDER BY count DESC
GROUP BY user_id

2.) Versuch
SELECT SUM(p.account_points_type) AS plus, SUM(m.account_points_type) AS minus, u.user_name, u.user_status
FROM tabelle_users AS u
LEFT JOIN tabelle_accounts AS p ON (u.user_id=p.account_owner AND p.account_points_type='1')
LEFT JOIN tabelle_accounts AS m ON (u.user_id=m.account_owner AND m.account_points_type='0')
ORDER BY (plus - minus) DESC
GROUP BY user_id

Usertabelle
user_id | user_name | user_status
-----------+-----------+-------------
1 | Musterm1 | 0
-----------+-----------+-------------
2 | Musterm2 | 0

Punktetabelle
account_id | account_owner | account_points_count | account_points_type | account_timestamp
------------+---------------+----------------------+---------------------+-------------------
1 | 1 | 100 | 1 | unix_timestmap ..
------------+---------------+----------------------+---------------------+-------------------
2 | 1 | 70 | 0 | unix_timestmap ..
------------+---------------+----------------------+---------------------+-------------------
3 | 1 | 25 | 0 | unix_timestmap ..
------------+---------------+----------------------+---------------------+-------------------
4 | 2 | 200 | 1 | unix_timestmap ..
------------+---------------+----------------------+---------------------+-------------------
5 | 2 | 100 | 0 | unix_timestmap ..
------------+---------------+----------------------+---------------------+-------------------
6 | 2 | 75 | 0 | unix_timestmap ..

Bin über jede Hilfe oder jeden Tipp dankbar!

Danke, liebe Grüße, David

 

[ukulele]

Du kannst nicht in einer ausgegebenen Spalte eines Selects auf eine andere ausgegebene Spalte des selben Selects zugreifen. Du hast also zwei Möglichkeiten:

1) Select schachteln:

SELECT t.plus - t.minus AS count FROM (
SELECT SUM(p.account_points_type) AS plus, SUM(m.account_points_type) AS minus FROM tabelle
) t

oder 2)

SELECT SUM(p.account_points_type) AS plus, SUM(m.account_points_type) AS minus, SUM(p.account_points_type) - SUM(m.account_points_type) AS count FROM tabelle

 

[David93]

Hallo Ukulele,

habe es nun wie gefolgt versucht, jedoch leider ohne Erfolg ..

SELECT p.count - m.count AS count, u.user_name
FROM ".DB_USERS." AS u,
(SELECT SUM(account_points_count) AS count FROM ".DB_PREFIX."ppps_accounts WHERE account_owner=u.user_id AND account_points_type='1') AS p,
(SELECT SUM(account_points_count) AS count FROM ".DB_PREFIX."ppps_accounts WHERE account_owner=u.user_id AND account_points_type='0') AS m
ORDER BY count DESC

Es wird u.user_id in der Where-Klausel nicht erkannt ..
Habe eben zuerst gesehen, dass ich in den ersten MySQL - Code was ich hier gepostet habe, auch einen Fehler bei den Fledernamen hatte, leider ist bearbeiten nicht mehr möglich ...

Danke vorab!

 

[ukulele]

Dein Code ist auch echt zum gruseln, probier mal das hier:

SELECT    u.[user_name],
        sum(p.account_points_count) - sum(m.account_points_count) AS [count]
FROM    ".DB_USERS." u
LEFT JOIN ".DB_PREFIX."ppps_accounts p
ON        p.account_owner = u.user_id
AND        p.account_points_type = '1'
LEFT JOIN ".DB_PREFIX."ppps_accounts m
ON        m.account_owner = u.user_id
AND        m.account_points_type = '0'
GROUP BY u.[user_name]
ORDER BY sum(p.account_points_count) - sum(m.account_points_count) DESC

 

[David93]

Also, der Code bringt schon mal keine Fehlermeldung, aber leider berechnet er falsch :/

So sieht meine Tabelle aus:

 account_owner | account_points_count | account_points_type
---------------+----------------------+---------------------
      1        |         50           |          1
---------------+----------------------+---------------------
      1        |         50           |          1
---------------+----------------------+---------------------
      1        |         10           |          1
---------------+----------------------+---------------------
      1        |         10           |          1
---------------+----------------------+---------------------
      1        |         20           |          1
---------------+----------------------+---------------------
      1        |         40           |          0
---------------+----------------------+---------------------
      2        |         50           |          1

Würde ergeben:
- User1 (140-40) = 100
- User2 (50-0) = 50

Ausgabe des Scripts leider:
- User1 -60
- User2 garkeine

Ausgeben tue ich es logischerweise per While-Schleife.

SELECT u.user_name, sum(p.account_points_count) - sum(m.account_points_count) AS count
FROM ".DB_USERS." u
LEFT JOIN ".DB_PREFIX."ppps_accounts p ON p.account_owner=u.user_id AND p.account_points_type='1'
LEFT JOIN ".DB_PREFIX."ppps_accounts m ON m.account_owner=u.user_id AND m.account_points_type='0'
WHERE u.user_status='0'
GROUP BY u.user_id
ORDER BY sum(p.account_points_count) - sum(m.account_points_count) DESC

 

[ukulele]

Stimmt doppelter LEFT JOIN hat mich selbst rein gelegt:

SELECT    u.[user_name],
        isnull(p.account_points_count,0) - isnull(m.account_points_count,0) AS [count]
FROM    users u
LEFT JOIN (    SELECT    account_owner,
                    sum(account_points_count) AS account_points_count
            FROM    ppps_accounts
            WHERE    account_points_type = '1'
            GROUP BY account_owner ) p
ON        p.account_owner = u.user_id
LEFT JOIN (    SELECT    account_owner,
                    sum(account_points_count) AS account_points_count
            FROM    ppps_accounts
            WHERE    account_points_type = '0'
            GROUP BY account_owner ) m
ON        m.account_owner = u.user_id
ORDER BY p.account_points_count - m.account_points_count DESC

Es gibt vermutlich noch 10 Rechenwege...

 

[David93]

Hallo Ukulele,

habe es noch minimal bearbeiten müssen
ISNULL() habe ich aufgrund der Meldung "Incorrect parameter when using isnull" in IFNULL() geändert, und es funktionierte fast.

Anschließend habe ich noch das ORDER BY zu ORDER BY count DESC geändert

Nun funktioniert es tadellos, vielen vielen Dank!

Hast du eventuell einen Tipp, wo man den Umgang mit MySQL vertiefend lernen kann? (Buch etc?)

Lg David.

 

[ukulele]

Gibt viele gute Bücher zu SQL aber bei MySQL kenne ich mich nicht so aus. Für den Anfang reichen sicher auch ein paar Internet Tutorials zu Joins und Wikipedia.

Ich würde mich beim Vertiefen auch auf ein anderes SQL als MySQL fokusieren, ich finde MySQL kann viel zu wenig. Wenn du kannst, steig auf PostgreSQL um oder hol dir zumindest die Lektüre gleich dafür.

 

[David93]

Ok vielen Dank, ein Umstieg auf PostgreSQL ist leider nicht möglich, da es zum einen das CMS was ich verwende nicht unterstützt, zum anderen auch mein Webhosting Anbieter nicht zur Verfügung stellt.