[SQL/MySQL] Rekenen met de uitkomst van een count()

[Xerohumoris]

Hoi,

Voor een website waar ik mee bezig ben heb ik een redelijk ik een wat lastige query nodig. De manier waarop gesorteerd moet worden is wat complex en ik krijg het niet voor elkaar. De grootste reden is aangezien ik wil sorteren op een deling van twee counts die ik uitvoer.
Om het nog leuker te maken gaat de query over totaal twee tabellen.
Voor de query zoals ik hem nu heb kun je even kijken op Fiddle. Dan nu het probleem:

Ik counts leveren een op en neer waarde op. Nu wil ik neer delen door de op naar een sort variable om vervolgens op te sorteren. Dat sorteren is het probleem niet. Maar het delen wel.

Ik heb al twee dingen geprobeer. Als laatste element in de SELECT zet ik dan.

code:

(neer / op) AS sort

Andere optie die ik heb geprobeert maar ook niet werkt is:

code:

SELECT ... ((SELECT SUM()) AS neer / (SELECT SUM()) AS op) AS sort

Dit geeft ook een foutmelding. Ik kom er niet meer uit en Google leek ook geen vriend.

Heeft iemand hier een oplossing voor het probleem? Ook tips om de algehele query te verbeteren zijn welkom.

MvG
Daniël Huisman

[GlowMouse]

Wat dacht je van ORDER BY neer/op?

[Xerohumoris]

Doet het THNX. Maar is er ook een mogelijkheid dat ik het resultaat van de deling krijg?

[NMe]

Natuurlijk, maar dan moet je hem zowel selecten als erop orderen.

[Xerohumoris]

Ja en als ik hem in de select zet dan krijg ik de foutmelding dat 'op' en 'neer' niet bestaan. En het is wel de laatste die ik selecteer. Op en neer zijn dan al aangemaakt.

[GlowMouse]

Een beetje onlogisch is het wel, maar je kunt dan het beste met variabelen werken:

code:

SELECT argument.*, 
        gebruiker.voornaam, 
        gebruiker.achternaam, 
        @op := (SELECT SUM(case when argBeoordeling.react = 1 then 1 else 0 end) FROM argBeoordeling WHERE argBeoordeling.argid = argument.id) AS op,
        @neer := (SELECT SUM(case when argBeoordeling.react = 0 then 1 else 0 end) FROM argBeoordeling WHERE argBeoordeling.argid = argument.id) AS neer,
        @op/@neer
        FROM argument 
        JOIN gebruiker ON argument.gebruikerID = gebruiker.id
        ORDER BY argument.datum DESC

[NMe]

Waarom variabelen?

SQL:

SELECT SELECT SUM(...) AS neer, SELECT SUM(...) AS op, ...
...
ORDER BY op / neer

Je kan gewoon orderen op aliassen.

[Xerohumoris]

Het is klaar. Heb besloten dat ratio maar weer in php te berekenen. Dat was het makkelijkste.
Hieronder de query zoals hij nu is. Voor als iemand ooit een soort gelijk probleem heeft.

SQL:

SELECT argument.*, 
        gebruiker.voornaam, 
        gebruiker.achternaam, 
        (SELECT SUM(case when argBeoordeling.react = 1 then 1 else 0 end) FROM argBeoordeling WHERE argBeoordeling.argid = argument.id) AS op,
        (SELECT SUM(case when argBeoordeling.react = 0 then 1 else 0 end) FROM argBeoordeling WHERE argBeoordeling.argid = argument.id) AS neer
        FROM argument 
        JOIN gebruiker ON argument.gebruikerID = gebruiker.id
        WHERE argument.commodityId = :id
        AND argument.type = :koopverkoop
        ORDER BY neer / (op + neer) ASC, op DESC, neer ASC, argument.datum DESC

[Xiphalon]

Of

SQL:

SELECT 
  argument.*,
  beo.op,
  beo.neer
FROM
  argument
LEFT JOIN
(
  SELECT
    SUM(CASE WHEN iab.react = 1 THEN 1 ELSE 0 END) AS op,
    SUM(CASE WHEN iab.react = 0 THEN 1 ELSE 0 END) AS neer,
    iab.ArgumentId AS argid 
  FROM
    argBeoordeling AS iab
  GROUP BY
    iab.ArgumentId
) AS beo ON beo.argid = argument.id
ORDER BY beo.neer / (beo.op + beo.neer) ASC, beo.op DESC, beo.neer ASC

Met de juiste indexen wordt dit razendsnel

[ Voor 8% gewijzigd door Xiphalon op 20-10-2013 14:38 . Reden: of je doet de ORDER BY er ook by ]

[Xerohumoris]

Alleen erg jammer dat hij niet lijk te werken:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'beo.op, beo.neer FROM argument LEFT JOIN ( SELECT SUM(CASE WHEN iab.re' at line 3: SELECT argument.* beo.op, beo.neer FROM argument LEFT JOIN ( SELECT SUM(CASE WHEN iab.react = 1 THEN 1 ELSE 0 END) AS op, SUM(CASE WHEN iab.react = 0 THEN 1 ELSE 0 END) AS neer, iab.ArgumentId AS argid FROM argBeoordeling iab GROUP BY iab.ArgumentId ) beo ON beo.argid = argument.id

[Xiphalon]

hmm, er miste een komma na argument.*

[Xerohumoris]

Dat is niet het enige. Ook als ik op andere plekken nog comma's invul gaat het fout. Moet er namelijk geen comma tussen argBeoordeling en iab? DAn krijg ik de melding dat iab en beo niet bestaan...

Maar ik ben het idd met je eens dat als je dit aan de praat krijgt er een erg snelle query ontstaat.

[The Eagle]

Dat je het delen noet voor elkaar krijgt komt waarschijnlijk omdat je voor op en neer een 1 resp 0 gebruikt. En delen door nul gaat niet natuurlijk

[Xiphalon]

Delen door nul kan je afvangen door ISNULL(NULLIF(<waarde>,0), <standaardwaarde>) te gebruiken, als <waarde> dan 0 is, wordt <standaardwaarde> gebruikt

Die query zou gewoon moeten werken, maar misschien is MySQL wat kritischer met AS?

[GlowMouse]

NMe schreef op zondag 20 oktober 2013 @ 13:27:
Waarom variabelen?

SQL:

SELECT SELECT SUM(...) AS neer, SELECT SUM(...) AS op, ...
...
ORDER BY op / neer

Je kan gewoon orderen op aliassen.

Beetje rare post als je de vragen/antwoorden erboven leest.

darkmage schreef op zondag 20 oktober 2013 @ 13:48:
Met de juiste indexen wordt dit razendsnel

Waarom zulke dingen roepen als je niet zegt wat 'de juiste indexen' zijn? Ik zal het antwoord geven, omdat ze niet bestaan.

[NMe]

GlowMouse schreef op zondag 20 oktober 2013 @ 17:33:
[...]

Beetje rare post als je de vragen/antwoorden erboven leest.

Niet echt, want die hele case...when is overbodig. Als argBeoordeling.react alleen 1 of 0 kan zijn heb je voor "op" aan SUM(argBeoordeling.react) genoeg en voor "neer" aan COUNT(argBeoordeling.react) - SUM(argBeoordeling.react).

[GlowMouse]

NMe schreef op zondag 20 oktober 2013 @ 18:19:
[...]

Niet echt, want die hele case...when is overbodig. Als argBeoordeling.react alleen 1 of 0 kan zijn heb je voor "op" aan SUM(argBeoordeling.react) genoeg en voor "neer" aan COUNT(argBeoordeling.react) - SUM(argBeoordeling.react).

De variabelen waren nodig om op/neer in de resultset terug te krijgen.