SUM in JOIN query met 2 tabellen geeft error
Code (php)
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
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'
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
Je SELECT SUM(CASE ...) heeft kennelijk echter maar twee toestanden:
Code (php)
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)
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.
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.
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.
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)
1
2
3
2
3
SELECT
c.voorraad
[...]
c.voorraad
[...]
En dan wordt de eerste WHERE-clausule logischerwijs dus:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT
c.voorraad
[...]
WHERE
c.voorraad > 0
c.voorraad
[...]
WHERE
c.voorraad > 0
Alleen zitten we hier met de prijs (subtotaal) die alles gaat rekenen, ook de items die niet in stock zijn.
Het is ook niet nodig om de sum te aliassen want dit soort subqueries mogen maar één waarde teruggeven.
Dus:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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,
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
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)
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
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'
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
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.
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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
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
@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