[SQL] count problemen in een "group by"

[Verwijderd]

Probleemstelling. Ik heb een query zoals hieronder.
Deze laat mij keurig de waarden zien van NSO, NSO_CODE, ORDERS, AMOUNT, CURR_CODE en MONTH. NSO, NSO_CODE, CURR_CODE en MONTH.

Echter nu wil ik ook het totaal aantal werkelijke regels van alle orders zien, oftwel de count van "ol.line_number" hier krijg ik echter alleen telkens het zelfde te zien als het aantal orders (ORDERS). Hoe kan ik dit toch voor elkaar krijgen om dit op beeld te krijgen?

code:

select 
      nso.name NSO, 
      nso.nso_code,
      count(*) orders,
      SUM(ol.ordered_quantity * ol.unit_selling_price) amount,
      oh.transactional_curr_code curr_code,
      to_char(oh.ordered_date, 'yyyy-mm') month
from 
    apps.oe_order_lines_all ol,
    apps.oe_order_headers_all oh,
    b2bcanon.cbb_nsos nso
where 
     ol.header_id = oh.header_id
     and oh.org_id = nso.org_id
     and oh.attribute15 IS NOT NULL
     and nvl(oh.cancelled_flag, 'N') <> 'Y'
     and nvl(oh.open_flag, 'Y') = 'Y'
     and nvl(ol.cancelled_flag, 'N') <> 'Y'
     and nvl(ol.open_flag, 'Y') = 'Y'
     and oh.ordered_date >= nvl('01-JAN-1900', '01-JAN-1900')
     and trunc(oh.ordered_date) <= nvl('01-JUL-2005', sysdate)
group by 
        nso.name,
        nso.nso_code,
        oh.transactional_curr_code,
        to_char(oh.ordered_date, 'yyyy-mm')

[mphilipp]

Je wilt het aantal orderregels zien van de orderlines die deze query produceert? Dan moet je de orderlinestabel nog een keer joinen (wel de juiste condities natuurlijk) en die alleen ook in de group by opnemen (by orderlines denk ik dan) en een count van die tabel in de select clause.

That should do the trick.

[Salandur]

count(*) veranderen in count(ol.line_number)

[Verwijderd]

count(ol.line_number) blijft het zelfde aantal geven als het aantal nummers. namelijk 22.

sum(ol.line_number) geeft 60 weer aangezien een line number 1, 2, 3 of een nog hoger getal kan geven. Ik dacht even dat inderdaad een count(ol.line_numer) de oplossing zou zijn maar helaas werkt dat ook niet.

Hoe was precies de gedacht om een dergelijke join te maken als vraag op de eventuele join oplossing?

[D4Skunk]

Verwijderd schreef op maandag 25 juli 2005 @ 16:46:

code:

...     
and oh.ordered_date >= nvl('01-JAN-1900', '01-JAN-1900')
and trunc(oh.ordered_date) <= nvl('01-JUL-2005', sysdate)
...

Dit klopt niet imho en moet zijn :

code:

...     
and nvl(oh.ordered_date,'01-JAN-1900')  >= '01-JAN-1900'
and nvl(trunc(oh.ordered_date),'01-JUL-2005')<= sysdate
...

[Verwijderd]

Je zult deze gegevens gewoon alleen met lelijk geflans erin krijgen. Veel beter is het om gewoon een losse query te draaien om dit resultaat te krijgen. Als alternatief kan je bij de verwerking van de resulterende records de count(ol.line_number) optellen en te delen door het aantal orders.

[mphilipp]

Verwijderd schreef op maandag 25 juli 2005 @ 17:39:
Hoe was precies de gedacht om een dergelijke join te maken als vraag op de eventuele join oplossing?

[edit]
Als je 'mijn' oplossing bedoelt...dat was geen goede...

Ik heb 'm dan ook weggekiept. Ik was aan het raaskallen. Heb inmiddels wat gegeten en gechilled en ben weer wat helderderder. Wat ik deed was het totaal aan orderregels voor die order op de regel afdrukken. Da's niet wat je wilt.

Het is toch iets lastiger, hoewel mijn oplossing wel in de buurt komt. Punt is dat eigenlijk dit wil:

code:

select 
      count(ol.*) orderregels
from 
    apps.oe_order_lines_all ol,
    apps.oe_order_headers_all oh
where 
     ol.header_id = oh.header_id
     and oh.attribute15 IS NOT NULL
     and nvl(oh.cancelled_flag, 'N') <> 'Y'
     and nvl(oh.open_flag, 'Y') = 'Y'
     and nvl(ol.cancelled_flag, 'N') <> 'Y'
     and nvl(ol.open_flag, 'Y') = 'Y'
     and oh.ordered_date >= nvl('01-JAN-1900', '01-JAN-1900')
     and trunc(oh.ordered_date) <= nvl('01-JUL-2005', sysdate)

(Maar dan zonder die gekke nvl... )
Dit ding wil je integreren in je oorspronkelijke query. Da's dus inderdaad een beetje lastig. Niet lastig als in onmogelijk, maar het wordt dan wel een hork van een query. In een nutshell komt het erop neer dat je 'mijn' query gewoon zo aan 'jouw' query moet plakken en moet voorzien van andere aliassen (anders komt het natuurlijk in de war...). Zowat alles dubbel dus,en dat alleen maar voor één kolommetje erbij. Een andere oplossing zie ik zo effe niet.

In Oracle zou ik dit in PL/SQL oplossen maar ik denk niet dat je op zo'n antwoord zat te wachten.

[ Voor 58% gewijzigd door mphilipp op 25-07-2005 21:29 ]

[mphilipp]

Verwijderd schreef op maandag 25 juli 2005 @ 18:40:
Je zult deze gegevens gewoon alleen met lelijk geflans erin krijgen.

Mijn oplossing is geen lelijk geflans...
[edit]
dus wél...

[ Voor 5% gewijzigd door mphilipp op 25-07-2005 21:30 ]

[mphilipp]

D4Skunk schreef op maandag 25 juli 2005 @ 18:20:
[...]


Dit klopt niet imho en moet zijn :

code:

...     
and nvl(oh.ordered_date,'01-JAN-1900')  >= '01-JAN-1900'
and nvl(trunc(oh.ordered_date),'01-JUL-2005')<= sysdate
...

Ik denk dat je gelijk hebt. nvl('hallo','ja daag') slaat idd nergens op. Mag ik evenwel aantekenen dat een nvl op een eventueel geïndexeerde kolom niet aan te bevelen is (ik kan dat toch niet laten als ex-docent). Pak dan een constructie met iets als:

code:

where (datum > to_date('1-jan-2005','dd-mon-yyyy') or datum is null)

en varianten hierop. Tenminste...bij Oracle gebruikt ie geen index als er een functie op de geïndexeerde kolom zit. En dat is jammer.

[Verwijderd]

Eigenlijk ben ik wel blij dat het probleem toch wat moelijker blijkt dan zo op het eerste oog. ik dacht al heb ik er nu zo over de oplossing heen gekeken... ;-)

Ik ga zodra ik morgen weer op de zaak aan verder om te kijken of dit de oplossing is.... Ik hou jullie op de hoogte. Ik ben op zoek naar een zo mooi mogelijke oplossing maar ik geloof dat het nooit echt een mooie query kan worden..... :-)


Ik hou jullie posted.

[Verwijderd]

Toch even wat code zitten plakken zonder het te kunnen testen op de database maar we zouden dus een query krijgen zoals hieronder....

Of hebben mensen hier nog een toevoeging op?

code:

select 
      nso.name NSO, 
      nso.nso_code,
      count(*) orders,
      count(oli.*) orderregels,
      SUM(ol.ordered_quantity * ol.unit_selling_price) amount,
      oh.transactional_curr_code curr_code,
      to_char(oh.ordered_date, 'yyyy-mm') month
from 
    apps.oe_order_lines_all ol,
    apps.oe_order_headers_all oh,
    b2bcanon.cbb_nsos nso,
    apps.oe_order_lines_all oli,
    apps.oe_order_headers_all oh
where 
     ol.header_id = oh.header_id
     and oh.org_id = nso.org_id
     and oh.attribute15 IS NOT NULL
     and nvl(oh.cancelled_flag, 'N') <> 'Y'
     and nvl(oh.open_flag, 'Y') = 'Y'
     and nvl(ol.cancelled_flag, 'N') <> 'Y'
     and nvl(ol.open_flag, 'Y') = 'Y'
     and oh.ordered_date >= nvl('01-JAN-1900', '01-JAN-1900')
     and trunc(oh.ordered_date) <= nvl('01-JUL-2005', sysdate)
     and oli.header_id = ohe.header_id
     and ohe.attribute15 IS NOT NULL
     and nvl(ohe.cancelled_flag, 'N') <> 'Y'
     and nvl(ohe.open_flag, 'Y') = 'Y'
     and nvl(oli.cancelled_flag, 'N') <> 'Y'
     and nvl(oli.open_flag, 'Y') = 'Y'
     and ohe.ordered_date >= nvl('01-JAN-1900', '01-JAN-1900')
     and trunc(ohe.ordered_date) <= nvl('01-JUL-2005', sysdate)
group by 
        nso.name,
        nso.nso_code,
        oh.transactional_curr_code,
        to_char(oh.ordered_date, 'yyyy-mm')

[ Voor 12% gewijzigd door Verwijderd op 25-07-2005 21:45 ]

[Verwijderd]

Ik heb de code zoals gisteren besproken nu op het systeem getest maar helaas. werkte de code. Na een kleine aanpassing om de code werkende te krijgen niet. De performance dropt verschrikkelijk en ik krijg nog steeds verkeerde resultaten terug.

Ook heb ik de code nog wat meer aangepast en ik krijg nu wel sneller de resultaten terug maar nog steeds de verkeerde. De sneller code, ik zal hem dan toch maar even posten, ziet er zo uit:

code:

select 
      nso.name NSO, 
      nso.nso_code,
      count(*) orders,
      count(oli.line_number) orderregels,
      SUM(ol.ordered_quantity * ol.unit_selling_price) amount,
      oh.transactional_curr_code curr_code,
      to_char(oh.ordered_date, 'yyyy-mm') month
from 
    apps.oe_order_lines_all ol,
    apps.oe_order_headers_all oh,
    b2bcanon.cbb_nsos nso,
    apps.oe_order_lines_all oli
where 
     ol.header_id = oh.header_id
     and oh.org_id = nso.org_id
     and oh.attribute15 IS NOT NULL
     and nvl(oh.cancelled_flag, 'N') <> 'Y'
     and nvl(oh.open_flag, 'Y') = 'Y'
     and nvl(ol.cancelled_flag, 'N') <> 'Y'
     and nvl(ol.open_flag, 'Y') = 'Y'
     and oh.ordered_date >= nvl('01-JAN-1900', '01-JAN-1900')
     and trunc(oh.ordered_date) <= nvl('01-JUL-2005', sysdate)
     and oli.header_id = oh.header_id
     and nvl(oli.cancelled_flag, 'N') <> 'Y'
     and nvl(oli.open_flag, 'Y') = 'Y'
group by 
        nso.name,
        nso.nso_code,
        oh.transactional_curr_code,
        to_char(oh.ordered_date, 'yyyy-mm')

Ergens zitten er ook dubbel tellingen in.

Iemand nog andere slimme oplossingen?

[Verwijderd]

SQL:

SELECT oh.order_type_id, COUNT(DISTINCT order_number), COUNT(line_id)
  FROM oe_order_headers oh
      ,oe_order_lines   ol
WHERE oh.header_id = ol.header_id      
GROUP BY order_type_id

SUNTAC, dit moet je zeker op het goede pad zetten, ik heb hier wel een group by op order type, hier moet jij natuurlijk je nso stuff zetten.

offtopic:
aha, nog een Oracle Apps Developer op GoT

[Swa-baldie]

In Oracle heb je (vanaf 9i als ik me niet vergis) window and analytic functions waarmee je dit netjes in 1 query op kunt lossen. Heb zo even geen voorbeeld bij de hand. Kijk anders even op asktom.oracle.com

[Verwijderd]

Volgens mij levert je count(*) inderdaad het aantal orderregels en niet het aantal orders op. Aantal orders doe je in deze query lijkt me met count(DISTINCT oh.header_id).

Resultaat :

code:

select 
      nso.name NSO, 
      nso.nso_code,
      count(*) orderlines,
      count(DISTINCT oh.header_id) orders,
      SUM(ol.ordered_quantity * ol.unit_selling_price) amount,
      oh.transactional_curr_code curr_code,
      to_char(oh.ordered_date, 'yyyy-mm') month
from 
    apps.oe_order_lines_all ol,
    apps.oe_order_headers_all oh,
    b2bcanon.cbb_nsos nso
where 
     ol.header_id = oh.header_id
     and oh.org_id = nso.org_id
     and oh.attribute15 IS NOT NULL
     and nvl(oh.cancelled_flag, 'N') <> 'Y'
     and nvl(oh.open_flag, 'Y') = 'Y'
     and nvl(ol.cancelled_flag, 'N') <> 'Y'
     and nvl(ol.open_flag, 'Y') = 'Y'
     and oh.ordered_date >= nvl('01-JAN-1900', '01-JAN-1900')
     and trunc(oh.ordered_date) <= nvl('01-JUL-2005', sysdate)
group by 
        nso.name,
        nso.nso_code,
        oh.transactional_curr_code,
        to_char(oh.ordered_date, 'yyyy-mm')

[Verwijderd]

Het klopt als een bus. Het werkt nu met de volgende code er in.

code:

     COUNT(DISTINCT ol.header_id) orders,
     COUNT(*) lines,

[curry684]

* curry684 vloekt even ongemerkt op dat niemand [code=sql] gebruikt

[Verwijderd]

DISTINCT in een agregate function ..... nooit geweten dat dat kon, hoewel ik me afvraag of het in alle RDBMS werkt. Zo leer je weer wat nieuws ...

/me troost curry, en wijst hem op zijn mogelijkheid om een post te editten en een Admin break erbij te zetten ter aanmoediging van goed gebruik van deze functionaliteit

[ Voor 3% gewijzigd door Verwijderd op 26-07-2005 14:58 ]