SUM in JOIN query met 2 tabellen geeft error

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Brecht S

Brecht S

07/10/2016 13:36:16
Quote Anchor link
Ik heb de volgende query:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
               SELECT
                   c.voorraad,
                   c.id as cid,
                   cb.artikel as cbartikel,
                   cb.prijs_lid as cbprijs_lid,
                   cb.prijs_niet_lid as cbprijs_niet_lid,
                   cb.studierichting as cbstudierichting,
                   cb.studentid,
                      
                      
                      (SELECT
                          SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid
                       FROM
                          cursusdienst_bestellingen cb
                       JOIN
                          cursusdienst c
                       ON
                          cb.cursus_id = c.id
                       WHERE
                          datum_verwijderd IS NULL AND
                          datum_reservatie_mail IS NOT NULL AND
                          datum_afhaling IS NULL AND
                          datum_afhaling_mail IS NULL AND
                          studentid = '$studentid' AND
                          cursus_id = '$cid'
                      ) as subtotaal_lid,
                  
                  
                  
                   (SELECT SUM(prijs_niet_lid) FROM cursusdienst_bestellingen WHERE datum_verwijderd IS NULL AND datum_reservatie_mail IS NOT NULL AND datum_afhaling IS NULL AND datum_afhaling_mail IS NULL AND studentid = '$studentid' AND cursus_id = '$cid') as subtotaal_niet_lid
                 FROM
                   cursusdienst c
                 JOIN
                   cursusdienst_bestellingen cb
                 ON
                   cb.cursus_id = c.id
                 WHERE
                   c.id = '$cid' AND cb.datum_verwijderd IS NULL AND cb.datum_afhaling IS NOT NULL AND cb.datum_afhaling_mail IS NULL AND cb.studentid = '$studentid'


Met deze regel krijg ik een fout: SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid
Als ik deze gebruik krijg ik de error: column prijs_lid in field list is ambiguous en als ik er cb.prijs_lid van maak (wat het eindelijk zou moeten zijn) krijg ik de error: operand contains more than 1 column

Ik heb ook 2x subtotaal_lid, misschien heeft het daar ook iets mee te maken?
Gewijzigd op 07/10/2016 13:36:49 door Brecht S
 
PHP hulp

PHP hulp

29/03/2024 14:21:32
 
Brecht S

Brecht S

10/10/2016 10:43:52
Quote Anchor link
Iemand een idee wat er hier fout gaat? Ik kan het niet vinden.
 
Ward van der Put
Moderator

Ward van der Put

10/10/2016 12:25:22
Quote Anchor link
SUM() is een aggregate function en die kun je nooit zonder GROUP BY gebruiken.

Je SELECT SUM(CASE ...) heeft kennelijk echter maar twee toestanden:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
SELECT SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid

Dan kun je bijvoorbeeld een IF() gebruiken:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
SELECT IF(c.voorraad > 0, prijs_lid, 0) AS subtotaal_lid

Bepaald logisch is dat echter niet: je zegt nu dat iets gratis is wanneer het niet op voorraad is. Daarmee krijg je later onherroepelijk problemen wanneer je bijvoorbeeld vandaag al iets verkoopt dat morgen in het magazijn binnenkomt. Ik denk daarom dat je de verbetering wat hogerop moet zoeken in je datamodel, niet in deze ene query.
 
Brecht S

Brecht S

10/10/2016 13:16:26
Quote Anchor link
Wat ik eigenlijk wil doen is een prijs opgeven van alle artikelen die op voorraad zijn.
Diegene die niet op voorraad zijn komen ook niet in de lijst. Voor de artikelen zelf lijkt dit goed te lukken maar voor de andere de voorraad < 0 telt hij bij het subtotaal toch de totaalsom. En daar gaat het dus fout.

Toevoeging op 10/10/2016 14:02:26:

En dan hebben we nog het probleem dat subtotaal_lid 2x voorkomt.
 
Ward van der Put
Moderator

Ward van der Put

10/10/2016 15:32:13
Quote Anchor link
Brecht S op 10/10/2016 13:16:26:
Wat ik eigenlijk wil doen is een prijs opgeven van alle artikelen die op voorraad zijn.
Diegene die niet op voorraad zijn komen ook niet in de lijst.

Dan staat de logisch conditie toch al helemaal aan het begin?
Je begint namelijk nu met:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
SELECT
  c.voorraad
  [...]

En dan wordt de eerste WHERE-clausule logischerwijs dus:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
SELECT
  c.voorraad
  [...]
WHERE
  c.voorraad > 0
 
Brecht S

Brecht S

10/10/2016 19:32:23
Quote Anchor link
@Ward: ik ben niet helemaal mee wat je wil zeggen maar het probleem zit hem volgens mij in de subquery want die gaat de prijs gaan bepalen (subtotaal). De rest van de query lijkt perfect te werken aangezien die de artikelen mooi gaat weergeven wat in voorraad is komt tevoorschijn en wat er niet is komt niet in de artikelenlijst.
Alleen zitten we hier met de prijs (subtotaal) die alles gaat rekenen, ook de items die niet in stock zijn.
 
Brecht S

Brecht S

11/10/2016 19:50:44
Quote Anchor link
Iemand een idee?
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

12/10/2016 14:39:12
Quote Anchor link
Bij een subquery in de select of in de where moet je de tabellen aliassen geven die je niet in de hoofdquery gebruikt.
Het is ook niet nodig om de sum te aliassen want dit soort subqueries mogen maar één waarde teruggeven.
Dus:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
    (SELECT
      SUM(CASE WHEN c1.voorraad > 0 THEN cb1.prijs_lid ELSE 0 END)
    FROM
      cursusdienst_bestellingen cb1
    JOIN
      cursusdienst c1
    ON
      cb1.cursus_id = c1.id
    WHERE
      datum_verwijderd IS NULL AND
      datum_reservatie_mail IS NOT NULL AND
      datum_afhaling IS NULL AND
      datum_afhaling_mail IS NULL AND
      studentid = '$studentid' AND
      cursus_id = '$cid'
    ) as subtotaal_lid,


@Ward
Indien er alleen aggegrate functies in de SELECT staan is een GROUP BY over de gehele resultset, en moet je die zelfs weglaten.

De IF-statement is geen standaard SQL, CASE wel.
Gewijzigd op 12/10/2016 14:39:46 door Ger van Steenderen
 
Brecht S

Brecht S

12/10/2016 16:53:04
Quote Anchor link
Lost dit mijn probleem op met dat de subtotaal_lid geen juiste waarde weergeeft?
Het subtotaal_lid telt momenteel alle waarden ook diegene waarvan de voorraad kleiner is dan 0. En dat mag dus niet.

Toevoeging op 13/10/2016 11:01:29:

Ik heb volgende geprobeerd:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
select
  c.voorraad,
  c.id as cid,
  cb.artikel as cbartikel,
  cb.prijs_lid as cbprijs_lid,
  cb.prijs_niet_lid as cbprijs_niet_lid,
  cb.studierichting as cbstudierichting,
  cb.studentid,
  case when c.voorraad > 0 then
    (
      select
        sum(prijs_lid)
       from cursusdienst_bestellingen cbx
       where cbx.cursus_id = cb.cursus_id
         and cbx.studentid = cb.studentid
         and cbx.datum_afhaling is null
         and cbx.datum_afhaling_mail is null
         and cbx.datum_reservatie_mail is not null
         and cbx.datum_verwijderd is null
    )
  else 0 end as subtotaal_lid,
  case when c.voorraad > 0 then
    (
      select
        sum(prijs_niet_lid)
       from cursusdienst_bestellingen cbx
       where cbx.cursus_id = cb.cursus_id
         and cbx.studentid = cb.studentid
         and cbx.datum_afhaling is null
         and cbx.datum_afhaling_mail is null
         and cbx.datum_reservatie_mail is not null
         and cbx.datum_verwijderd is null
    )
  else 0 end as subtotaal_niet_lid
from cursusdienst c
join cursusdienst_bestellingen cb on cb.cursus_id = c.id
where cb.datum_afhaling is not null
  and cb.datum_afhaling_mail is null
  and cb.datum_verwijderd is null
  and cb.studentid = '$studentid'
  and c.id = '$cid'


Maar het resultaat blijft hetzelfde. De waarde van subtotaal_lid is nog steeds alle artikelen, ook diegene die niet in stock zijn.
Gewijzigd op 12/10/2016 16:59:51 door Brecht S
 
Pg Vincent

Pg Vincent

14/10/2016 15:47:18
Quote Anchor link
Wat staat er precies in c.voorraad als er geen voorraad is? je controleert nu op >0, wat betekent dat er een getal van 0 of lager in moet staan, anders gaat je CASE wel af en krijg je de sum uit de subquery.

Verder: niet alles moet altijd in dezelfde query. Je kunt ook twee queries draaien, een met records die wel op voorraad zijn, en een met records die niet op voorraad zijn, en die met UNION aan elkaar plakken.

Nog verder: waarom sla je het totaal niet gewoon op in de tabel zelf? Zodra een bestelling is geplaatst mag de prijs niet meer veranderen, en dus verandert het totaal ook niet meer.
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

16/10/2016 12:51:39
Quote Anchor link
Brecht S op 10/10/2016 19:32:23:
De rest van de query lijkt perfect te werken aangezien die de artikelen mooi gaat weergeven wat in voorraad is komt tevoorschijn en wat er niet is komt niet in de artikelenlijst.

Dat bevreemd mij want in de query die jij ons voorschotelt doe je alleen in de subqueries iets met voorraad.

Brecht S op 12/10/2016 16:53:04:
....
Maar het resultaat blijft hetzelfde. De waarde van subtotaal_lid is nog steeds alle artikelen, ook diegene die niet in stock zijn.


Dus jij zegt deze query records oplevert:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
    SELECT
        cb1.studentid,
        cb1.cursus_id,
        SUM(prijs_lid) AS subtotaal_lid,
        SUM(prijs_niet_lid) AS subtotaal_niet_lid
    FROM
        cursusdienst_bestellingen cb1
    JOIN
        cursusdienst c1
        ON cb1.cursus_id = c1.id
    WHERE
        c1.voorraad > 0 AND
        datum_verwijderd IS NULL AND
        datum_reservatie_mail IS NOT NULL AND
        datum_afhaling IS NULL AND
        datum_afhaling_mail IS NULL AND
        studentid = '$studentid' AND
        cursus_id = '$cid'
    GROUP BY studentid, cursus_id

Heel onwaarschijnlijk voor een artikel met voorraad <= 0
Gewijzigd op 16/10/2016 13:25:29 door Ger van Steenderen
 
Brecht S

Brecht S

17/10/2016 11:39:42
Quote Anchor link
c.voorraad is <= 0 als er geen voorraad is. Kan dus ook negatief zijn.
@Ger: zoals ik zie is de SUM van het totaal dus met alle records erbij, ook die waarvan de voorraad <= 0 en dat is niet juist. Dat moet inderdaad aan de subquery liggen want die doet alleen iets voor dat totaal te berekenen. Ik heb geen idee wat het kan zijn. Heb al overal zitten zoeken.
Gewijzigd op 17/10/2016 11:43:23 door Brecht S
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

17/10/2016 14:40:23
Quote Anchor link
Je moet het eerst zoeken in je datamodel, op het moment dat je een tabel met bestellingen moet gaan raadplegen om de prijs van een artikel te bepalen klopt er iets niet daarin.
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.