[SQL/IB] Probleem met sommatie en maximalisatie

[Verwijderd]

Hallo! Ik heb een probleempje met een query in Interbase.

Het gaat om één tabel (factuurregels), die er ongeveer zo uitziet.

artikelnr	prijs	factuurdatum	aantal
0001	5.38	1 dec 2003	1
0001	5.38	3 dec 2003	1
0001	5.38	5 dec 2003	1
0001	4.18	11 dec 2003	1

Nu moet de query de volgende output geven (1 regel dus!):

artikelnr	laatste prijs	totaal aantal
0001	4.18	4

Het lukt me niet met 1 query en ook een self join is nog niet gelukt. Is het zo dat ik dit per sé met twee queries moet doen, waarbij eentje de totalen berekent en de ander de laatste prijs? Oftewel, hoe maak ik er één query van?

[gorgi_19]

Subqueries gebruiken.

[Verwijderd]

Subquery als in een query in de WHERE-clause, betekent dat je een beperking oplegt, mijnsinziens. Volgens mij gaat dat niet op. (tried that too)

[ Voor 7% gewijzigd door Verwijderd op 17-12-2003 14:56 ]

[cavey]

je wilt de laagste laatste (ik blijf bezig) prijs weten?

SQL:

select artikelnr, prijs as [laatste prijs], sum(aantal) as [totaal aantal]
from factuurregels
group by artikelnr
having max(datum)

(maar nu begin ik te twijfelen)

als het goed is zou dat zo moeten kunnen werken .... eventueel moet je wat meer kolommen in group by gooien. Dit is trouwens Transact-SQL syntax, geen idee in hoeverre die [ ] dingen nog op andere plaatsen gebruikt worden. Kheb het maar even zo gedaan omdat ik te lui ben om door te zoeken hoe aliasing werkt bij interbase maar als je geen spaties gebruikt in je aliasing kan je die [ ] dingen weg halen.

suc6 (subqueries.. tsk... gewoon handig group by'n )

[toevoeginkje]
ach subquery kan ook wel hoor als je nog ergens een uniek id hebt staan in je factuurregels die primary key is zeg maar.

select * from factuurregels where factuurregelid in
( select FactuurRegelId group by artikelnr having min(prijs) )

ofzo... this is just out of the top of my head, kan er natuurlijk compleet naast zitten

[ Voor 30% gewijzigd door cavey op 17-12-2003 15:02 ]

[Verwijderd]

Nope, niet de laagste prijs, maar de laatste prijs. Hangt dus af van Factuurdatum. Een simpele max() of min() is dus niet van toepassing.

[gorgi_19]

komakeef schreef op 17 december 2003 @ 14:57:
suc6 (subqueries.. tsk... gewoon handig group by'n )

Is idd ook wel met een Group By en Min / Sum etc. te doen ja.. Alleen als je aparte constructies wilt met ongerelateerde zaken in 1 recordset, wordt het lastiger..

[ Voor 20% gewijzigd door gorgi_19 op 17-12-2003 15:00 ]

[hobbit_be]

moet je dan niet gewoon een where met daarin "laaste" tijd ? anders kan CASE WHEN enzo wel helpen...

[cavey]

Verwijderd schreef op 17 december 2003 @ 14:59:
Nope, niet de laagste prijs, maar de laatste prijs. Hangt dus af van Factuurdatum. Een simpele max() of min() is dus niet van toepassing.

zie m'n zoveelste keer ge-editte ding. hehehe... in weze maakt het niet zoveel uit... alleen moet je nu wellicht wel weer group by'n op prijs.

ik word namenlijk best wel gek van group by.....

even zien, mocht je nog problemen krijgen dan ff zo redelijk clean code hopelijk:

code:

select artikelnr, prijs, sum(aantal) as aantal
from factuurregels
having max(datum)
group by artikelnr, prijs

*ik trek hierbij een bedenkelijk gezicht... ymmv *

[gorgi_19]

Volgens mij selecteer je nu maar 1 record; namelijk diegene met de hoogste datum, en gaat je sum daardoor de mist in.

[Verwijderd]

Nee, volgens mij krijg je twee records, eentje met sum=3 en eentje met sum=1. Beide hebben een max_datum. Maar ze worden gegroepeerd op artikelnummer en prijs en d'r zijn twee prijzen...

(leuk hè, dit)

[ Voor 7% gewijzigd door Verwijderd op 17-12-2003 15:08 ]

[gorgi_19]

Verwijderd schreef op 17 december 2003 @ 15:07:
Nee, volgens mij krijg je twee records, eentje met sum=3 en eentje met sum=1. Beide hebben een max_datum. Maar ze worden gegroepeerd op artikelnummer en prijs en d'r zijn twee prijzen...

(leuk hè, dit)

Maar wat is er nu mis met het gebruik van een subquery?

[cavey]

nah, komt door de group by, dan zou alles goed moeten gaan. Maar ik ben te beroerd om ff een test tabelletje te maken genaamd factuur regels etc etc...

Heb laatst ook zoiets moeten maken waarbij ik alleen geinteresseerd was in het laatst toegevoegde veld op basis van een ander veld. even spieken ...

Hmm, haha, ok.. ik zie al wat ik gedaan heb. Ahem.

HOPELIJK heb je een factuurregelID veld zitten, anders loopt het beetje de soep in (wat is je primary key enzo?)

maar ik heb dus zoiets in mekaar gedraaid:

code:

select *
from factuurregels
where FRid in
(
  select max(FRid) as FRid
   from factuurregels
   group by artikelnr
)

goed, ymmv .... het is een idee.

[cavey]

Verwijderd schreef op 17 december 2003 @ 15:07:
Nee, volgens mij krijg je twee records, eentje met sum=3 en eentje met sum=1. Beide hebben een max_datum. Maar ze worden gegroepeerd op artikelnummer en prijs en d'r zijn twee prijzen...

(leuk hè, dit)

(nee, ik haat group by meestal als er "meerdere unieke" velden in zitten die ik wel in de output wil hebben maar dus eigenlijk NIET in de group by, maar op de een of de andere manier moet ik ze weer WEL in de group by hebben zitten want anders wordt het niet in de output gezet... kan het niet eens in de output gezet worden... stom gedoe)

[whoami]

komakeef schreef op 17 december 2003 @ 15:10:
nah, komt door de group by, dan zou alles goed moeten gaan. Maar ik ben te maar ik heb dus zoiets in mekaar gedraaid:

[code]
select *
from factuurregels
where FRid in
(
select max(FRid) as FRid
from factuurregels
group by artikelnr
)

Ook hier heb je geen SUM.

je zult -denk ik- een subquery in je SELECT list nodig hebben:

code:

select artikelnr, sum(aantal), (select max(datum) from tabel)
from tabel

Trouwens, met een GROUP BY is niks mis als je eens logisch nadenkt.
Een group by gebruik je alleen maar ism aggregated functions. Stel dat je bepaalde velden uit je select list kon weglaten uit je group by (als je dus een aggregaatfunctie gebruikt), welke waarde zou het DBMS dan moeten tonen ?

[Verwijderd]

Ja, zoiets dacht ik ook, maar Interbase ondersteunt dat niet volgens mij...

[Maasluip]

komakeef schreef op 17 december 2003 @ 15:10:
maar ik heb dus zoiets in mekaar gedraaid:

Waarom dan niet

code:

select artikelnr, prijs, factuurdatum, count(*)
from factuurregels
where factuurdatum =
(
  select max(factuurdatum)
   from factuurregels
)
group by artikelnr, prijs, factuurdatum

edit:
Damn, niet op de uitkomst van count(*) gelet

Werkt dus niet

[ Voor 10% gewijzigd door Maasluip op 17-12-2003 15:19 ]

[gorgi_19]

Omdat je Count dan in de mist gaat; je selecteert immers alleen de records met de hoogste factuurdatum.

[Maasluip]

Okee, testen of Interbase subqueries verstaat:

code:

select artikelnr, prijs, factuurdatum, s.cnt
from factuurregels,
(select count(*) cnt from factuurregels) s
where factuurdatum =
(
  select max(factuurdatum)
   from factuurregels
)

Dit werkt in Oracle.

[whoami]

mdeen schreef op 17 december 2003 @ 15:25:
Okee, testen of Interbase subqueries verstaat:

code:

select artikelnr, prijs, factuurdatum, s.cnt
from factuurregels,
(select count(*) cnt from factuurregels) s
where factuurdatum =
(
  select max(factuurdatum)
   from factuurregels
)

Dit werkt in Oracle.

Dat is min of meer dezelfde oplossing zoals ik al eerder geplaatst heb, met uitzondering dat mijn oplossing slechts 1 subquery doet, en de jouwe er 2 doet.
Daarnaast meld de topicstarter dat IB dit waarschijnlijk niet slikt, dus denk ik dat hij het dan maar met 2 queries moet oplossen.

[Verwijderd]

Testen in Interbase laat niet het bevredigende resultaat zien (incl. joins). Volgens mij verstaat Interbase geen SELECT-statements in de FROM-sectie...

Maar dat is wel de oplossing volgens mij, gewoon de boel splitsen en later joinen.

(die laatste subquery is vlg mij idd niet nodig)

[ Voor 10% gewijzigd door Verwijderd op 17-12-2003 15:28 ]

[Maasluip]

whoami schreef op 17 december 2003 @ 15:26:
[...]


Dat is min of meer dezelfde oplossing zoals ik al eerder geplaatst heb, met uitzondering dat mijn oplossing slechts 1 subquery doet, en de jouwe er 2 doet.
Daarnaast meld de topicstarter dat IB dit waarschijnlijk niet slikt, dus denk ik dat hij het dan maar met 2 queries moet oplossen.

Kweenie, maar jou query werkt in Oracle iig niet. Een select in de select werkt niet.

Verwijderd schreef op 17 december 2003 @ 15:27:(die laatste subquery is vlg mij idd niet nodig)

Wel in Oracle ben ik bang. Je kunt het daar niet oplossen met een

code:

where factuurdatum = max(factuurdatum)

Maar goed, ik praat hier Belgisch en jij Nederlands. Dan kan het botsen

[cavey]

ow ja die sum vergeten.. njaaaaaaaaa...

ik ben zo geneigd om de boel even snel in een test tabel te gooien in mssql.. ach wtf
ff tinkeren

[whoami]

mdeen schreef op 17 december 2003 @ 15:35:
[...]

Kweenie, maar jou query werkt in Oracle iig niet. Een select in de select werkt niet.

Nouja, Oracle is ook niet zaligmakend.
In Sql Server werkt zoiets bv wel.

[ Voor 22% gewijzigd door whoami op 17-12-2003 15:41 ]

[Maasluip]

whoami schreef op 17 december 2003 @ 15:36:
[...]

Euh, kwee ook nie hoor, maar jij doet het ook hoor. (Een Select in een select, met de vermelding dat het in oracle wel gaat).

Nee, ik doe een select in de from. Dat is net iets anders.

[ Voor 2% gewijzigd door Maasluip op 17-12-2003 15:39 . Reden: s/where/from ]

[bigbeng]

Ik heb de volgende query bedacht en getest onder MSSQL, kweenie of hij werkt onder InterBase of andere databases:

code:

select f.artikelnr,sum(f.aantal) as 'totaal aantal', max(f2.prijs) as 'laatste prijs'
from factuur f, factuur f2
where f.artikelnr=f2.artikelnr
and f2.factuurdatum IN (
    select max(factuurdatum) from factuur f3
    where f3.artikelnr = f.artikelnr
)
group by f.artikelnr

Leuk man, SQL puzzeltjes

[whoami]

mdeen schreef op 17 december 2003 @ 15:38:
[...]


Nee, ik doe een select in de from. Dat is net iets anders.

Ik had iets te snel gelezen.

[Maasluip]

bigbeng schreef op 17 december 2003 @ 15:41:
Ik heb de volgende query bedacht en getest onder MSSQL, kweenie of hij werkt onder InterBase of andere databases:

Dit werkt zelfs in Oracle, je bent alleen de datum vergeten

[bigbeng]

mdeen schreef op 17 december 2003 @ 15:44:
[...]

Dit werkt zelfs in Oracle, je bent alleen de datum vergeten

Goed... maar de datum hoefde er volgens mij niet bij, tenminste niet in de oorspronkelijke vraag.

Wel blijven opletten hoor

[Maasluip]

bigbeng schreef op 17 december 2003 @ 15:46:
[...]
Wel blijven opletten hoor

Sorry meester

[Verwijderd]

tering, dat ziet er niet leuk uit... performt dit wel een beetje? Wat is je output?\

[cavey]

..

hier stond een hoop blaat, maar omdat ik de prijs erbij vergeten was, werkte het natuurlijk weer voor geen meter......... uitstekend gedaan van mezelf. ahem.

[ Voor 97% gewijzigd door cavey op 17-12-2003 15:56 ]

[whoami]

Hmmm, 2 queries zal wel performanter zijn.

[cavey]

haha, die

select .....
from factuurregels f1, factuurregels f2

ik was dat helemaal vergeten ja, gewoon tabel meerdere keren "includen" met een andere naam. hehehe.

maar ik denk dat die oplossing met een view wel redelijk snel zou moeten performen ......maar goed, YMMV

[bigbeng]

Verwijderd schreef op 17 december 2003 @ 15:51:
tering, dat ziet er niet leuk uit... performt dit wel een beetje? Wat is je output?\

Hehe, ik was anders best trots op mijn werk...

Mijn output, met twee artikelnummers:

code:

artikelnr totaal aantal laatste prijs
0001      4          4.1800
0002      3          5.7500

tabel factuur:

code:

    0001    5,38    1-12-2003       1
    0001    5,38    3-12-2003       1
    0001    5,38    5-12-2003       1
    0001    4,18    11-12-2003      1
    0002    5,75    12-12-2003      2
    0002    4,45    11-12-2003      1

Kun je er wat mee?

[Verwijderd]

Zeker, ik ga de boel even uitproberen (originele tabel is wat uitgebreider, dus d'r moet nog wel wat gehackt worden...)

Thanks so far.

[Verwijderd]

Ik wilde nog even melden dat de query zoals bigbeng die presenteerde de basis heeft gelegd voor de uiteindelijke query. Ik ben nog een beetje sceptisch over de performance, maar vooralsnog heb ik daar geen klachten over gehoord. Hij draait in productie nu en je gelooft het niet, de mensen zijn blij hier.

Ik dank u allen.

[whoami]

1 query is niet altijd sneller dan 2 queries.

[Verwijderd]

True, maar het programma wat communiceert met de database accepteert maar 1 query om (in dit geval) het rapport te draaien. D'r zijn vast trucjes, maar in 1 query bespaar ik mezelf weer een hoop uitzoekwerk...