SUM in JOIN query met 2 tabellen geeft error

Iemand een idee wat er hier fout gaat? Ik kan het niet vinden.

SUM() is een aggregate function en die kun je nooit zonder GROUP BY gebruiken.

Je SELECT SUM(CASE ...) heeft kennelijk echter maar twee toestanden:

SELECT SUM(CASE WHEN c.voorraad > 0 THEN prijs_lid ELSE 0 END) AS subtotaal_lid

Dan kun je bijvoorbeeld een IF() gebruiken:

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.

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.

[size=xsmall]Toevoeging op 10/10/2016 14:02:26:[/size]

En dan hebben we nog het probleem dat subtotaal_lid 2x voorkomt.

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:

SELECT 
  c.voorraad
  [...]

En dan wordt de eerste WHERE-clausule logischerwijs dus:

SELECT 
  c.voorraad
  [...]
WHERE
  c.voorraad > 0

@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.

Iemand een idee?

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:

	(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.

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.

[size=xsmall]Toevoeging op 13/10/2016 11:01:29:[/size]

Ik heb volgende geprobeerd:

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.

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.

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:

	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