SQL via CASE-functionaliteit een opsomming weergeven

[JohanLi]

Hallo,

Ik heb een query waarin ik zoek naar inkooporders van een specifiek artikel in de toekomst tot een bepaalde datum; bijvoorbeeld twee weken vooruit.

Indien er meerdere inkooporders van dit artikel zijn, en ook nog binnen de range van twee weken; dan wil ik deze gebundeld weergeven. Dit dacht ik via de CASE-functionaliteit te doen. Het lukt mij echter niet om het gebundeld weer te geven. Elke inkooporder blijft als aparte regel weergegeven.

case
when LEVERDATUM < '20220420' then sum(HOEVEELHEID) end as "PO < 14",

Ook dit geeft niet het juiste resultaat:
sum(case
when LEVERDATUM < '20220420' then HOEVEELHEID end) as "PO < 14",


Ik kan nergens vinden welke software ik gebruik, het is een Query Builder in "IBS Enterprise v2.5.0.2". Ik kan enkel queries bouwen middels de SELECT- functinaliteit

[GarBaGe]

bundelen in SQL betekent gebruik maken van GROUP BY, waarbij je vervolgens bepaalde kolommen kan samenvoegen met commando's als SUM, COUNT of AVG.
Zie: https://www.w3schools.com/sql/sql_groupby.asp

Als je deels wilt groeperen en deels niet, dat kan niet in een enkele SQL statement.
Dan moet je 2 SQL statements maken en deze via UNION aan elkaar koppelen.

[ Voor 29% gewijzigd door GarBaGe op 05-04-2022 09:28 ]

[JohanLi]

Het is gelukt. Uiteindelijk probeerde ik in een CASE te doen wat veel eenvoudiger kon;

De HOEVEELHEID gewoon in de Select en vervolgens bij where by LEVERDATUM < 20220420

Echter...

Dat brengt mij weer tot de volgende uitdaging. Eén van de kolommen toont ook de datum van een inkooporder welke het verst in de toekomst ligt. Als in max(LEVERDATUM). Maar door nu bij where by LEVERDATUM < 20220420 te doen, wordt daar de juiste waarde niet meer getoond.

Kan ik de restrictie LEVERDATUM < 20220420 op de ene kolom wel toe passen en op de andere niet? En hoe dan?

[GarBaGe]

Hoe ziet je SQL query er nu uit, dan?

[CurlyMo]

Je LEVERDATUM verplaatsen van de where naar de having is het makkelijkst

[Tsurany]

Wat je dan kan doen is twee queries maken en die samenvoegen.

select a.hoeveelheid, b.max_leverdatum from
(select artikel, sum(hoeveelheid) from inkooporders group by artikel) a
join
(select artikel, max(leverdatum) from inkooporders group by artikel) b
on a.artikel = b.artikel

[KabouterSuper]

JohanLi schreef op dinsdag 5 april 2022 @ 09:02:
Hallo,

Ik heb een query waarin ik zoek naar inkooporders van een specifiek artikel in de toekomst tot een bepaalde datum; bijvoorbeeld twee weken vooruit.

Indien er meerdere inkooporders van dit artikel zijn, en ook nog binnen de range van twee weken; dan wil ik deze gebundeld weergeven. Dit dacht ik via de CASE-functionaliteit te doen. Het lukt mij echter niet om het gebundeld weer te geven. Elke inkooporder blijft als aparte regel weergegeven.

case
when LEVERDATUM < '20220420' then sum(HOEVEELHEID) end as "PO < 14",

Ook dit geeft niet het juiste resultaat:
sum(case
when LEVERDATUM < '20220420' then HOEVEELHEID end) as "PO < 14",


Ik kan nergens vinden welke software ik gebruik, het is een Query Builder in "IBS Enterprise v2.5.0.2". Ik kan enkel queries bouwen middels de SELECT- functinaliteit

Deze zou het toch echt moeten doen (mits je de juiste GROUP BY doet).

code:

sum(case 
when LEVERDATUM < '20220420' then  HOEVEELHEID end) as "PO < 14"

Je max(LEVERDATUM) kan je dan ook gewoon gebruiken.

[JohanLi]

GarBaGe schreef op dinsdag 5 april 2022 @ 11:15:
Hoe ziet je SQL query er nu uit, dan?

Select sum(HOEVEELHEID)
from ...
where LEVERDATuM < 20220420

Ik had veel te moeilijk gedacht...

[CurlyMo]

@JohanLi Daar zit je max toch niet in? Ga je daar de union voor toepassen of een having?

[JohanLi]

KabouterSuper schreef op dinsdag 5 april 2022 @ 12:22:
[...]

Deze zou het toch echt moeten doen (mits je de juiste GROUP BY doet).

code:

sum(case 
when LEVERDATUM < '20220420' then  HOEVEELHEID end) as "PO < 14"

Je max(LEVERDATUM) kan je dan ook gewoon gebruiken.

Daar gaat het dus mis, ik doe idd de juiste GROUP BY niet, maar kom er niet achter waar het mis gaat.

[KabouterSuper]

JohanLi schreef op dinsdag 5 april 2022 @ 12:33:
[...]


Daar gaat het dus mis, ik doe idd de juiste GROUP BY niet, maar kom er niet achter waar het mis gaat.

Er bestaan veel sql dialecten. Oracle geeft een foutmelding als je een kolom selecteert die niet in je group by zit en ook geen groepsfunctie is. MySql voegt deze kolom impliciet toe aan je group by.
Dus
Select id, leverdatum, sum(hoeveelheid) from tabel group by id
is fout, maar MySql maakt er dit van:
Select id, leverdatum, sum(hoeveelheid) from tabel group by id, leverdatum
Gevolg is dat je al je rijen terugkrijgt.

Maar er zijn nog veel meer dialecten, zoals teradata, ms access, sql,server, etc.

Ik vermoed dat je per ongeluk iets selecteert waardoor je group by verandert.

[JohanLi]

CurlyMo schreef op dinsdag 5 april 2022 @ 12:25:
@JohanLi Daar zit je max toch niet in? Ga je daar de union voor toepassen of een having?

Ah, ik dacht de oplossing tot op heden. Totale query is als volgt:

select ARTIKEL, VOORRAAD, ON SALESORDER, ON PURCHASE ORDER, count(PURCHASE ORDER), min(LEVERDATUM), max(LEVERDATUM) (VOORRAAD + PURCHASE ORDER - SALES ORDER) , SALES LAST YEAR, SALES THIS YEAR, sum (HOEVEELHEID)-SALES ORDER as "Tekort < 14 dgn"
from...
-- 1x inner join, 2x left outer join.
where LEVERDATUM < '20220420'

Group by ARTIKEL, VOORRAAD, SALES ORDER, PURCHASE ORDER, SALES LAST YEAR, SALES THIS YEAR

[JohanLi]

KabouterSuper schreef op dinsdag 5 april 2022 @ 12:40:
[...]

Er bestaan veel sql dialecten. Oracle geeft een foutmelding als je een kolom selecteert die niet in je group by zit en ook geen groepsfunctie is. MySql voegt deze kolom impliciet toe aan je group by.
Dus
Select id, leverdatum, sum(hoeveelheid) from tabel group by id
is fout, maar MySql maakt er dit van:
Select id, leverdatum, sum(hoeveelheid) from tabel group by id, leverdatum
Gevolg is dat je al je rijen terugkrijgt.

Maar er zijn nog veel meer dialecten, zoals teradata, ms access, sql,server, etc.

Ik vermoed dat je per ongeluk iets selecteert waardoor je group by verandert.

Dat vermoed ik ook, ik duik er nog eens in. Dank zo ver.

[CurlyMo]

JohanLi schreef op dinsdag 5 april 2022 @ 12:56:
[...]


Ah, ik dacht de oplossing tot op heden. Totale query is als volgt:

1. Gebruik code tags
2. Vervang je where eens door een having. Krijg je dan hetzelfde resultaat?

[GarBaGe]

Je breekt inderdaad een aantal belangrijke SQL regels.

1. Als je GROUP BY gebruikt, moeten AL je SELECT kolommen OF in de GROUP BY zitten OF een aggregatie functie zijn.
Alleen MySQL tolereert dit, door impliciet deze aan je GROUP BY toe te voegen.
Dat werd hierboven ook al vertelt.

2. Als je WHERE gebruikt, mag dit alleen op een reguliere SELECT kolom en NIET op een aggregatie.
Als je WHERE op een aggregatie wilt gebruiken, moet je HAVING inzetten.

Dus: SELECT min(LEVERDATUM) ... WHERE min(leverdatum) < '20220420' is fout
Dat moet dan: SELECT min(LEVERDATUM) ... HAVING min(leverdatum) < '20220420' zijn.
Zie: https://www.w3schools.com/sql/sql_having.asp

Je WHERE gebruik je dus om je resultaatset te filteren VOORDAT er een GROUP BY plaats vind.
Vandaar de volgorde in SQL:
WHERE
GROUP BY
HAVING
ORDER BY

Dat wordt strikt in die volgorde uitgevoerd.

Ik zie je query:
select ... min(LEVERDATUM), max(LEVERDATUM)...
from...
where LEVERDATUM < '20220420'
GROUP BY ...

je wilt alleen LEVERDATUM van VOOR 20220420.
je max(leverdatum) zal dus maximaal opleveren 20220419