Grouping by // Aggregate Function problem 1

[efinnen]

Hi All

First off let me state that any assistance that could be assistance/suggestions with this would be greatly appreciated… And I’ll give you the disclaimer to start. I’m an Access guy who is learning painfully that Access SQL isn’t quite the same as is required for Oracle..

But this works in Access…

What I am trying to do Is bring in data from four tables feed it two where statements specifying specific criteria and pull in the First TRIPID and the MAX of EFFCALC..

For what its worth, the Access query (with slightly different table names, but the same field names is below)

SELECT GEAR_02_03.LATITUDE_DD AS LAT_DD, GEAR_02_03.LONGITUDE_DD AS LONG_DD, Max([VPS_VESSEL]![EFFCALC]) AS EFF, GEAR_02_03.GEARCODE, TRIP_02_03.DATELND1, Format(TRIP_02_03!DATELND1,'yyyy_mm') AS Step, First(TRIP_02_03.TRIPID) AS FirstOfTRIPID
FROM SPECIES_02_03 INNER JOIN ((TRIP_02_03 INNER JOIN VPS_VESSEL ON TRIP_02_03.HULLNUM = VPS_VESSEL.HULL_ID) INNER JOIN GEAR_02_03 ON TRIP_02_03.TRIPID = GEAR_02_03.TRIPID) ON SPECIES_02_03.TRIPID = TRIP_02_03.TRIPID
WHERE (((SPECIES_02_03.SPPCODE)="POLL" Or (SPECIES_02_03.SPPCODE)="COD" Or (SPECIES_02_03.SPPCODE)="HADD" Or (SPECIES_02_03.SPPCODE)="FLYT" Or (SPECIES_02_03.SPPCODE)="FLGS" Or (SPECIES_02_03.SPPCODE)="FLDAB" Or (SPECIES_02_03.SPPCODE)="FLBB"))
GROUP BY GEAR_02_03.LATITUDE_DD, GEAR_02_03.LONGITUDE_DD, GEAR_02_03.GEARCODE, TRIP_02_03.DATELND1
HAVING (((GEAR_02_03.GEARCODE)="OTF" Or (GEAR_02_03.GEARCODE)="OTC"));

The problem is getting this working with Oracle..

At the moment this is what I have (that works but is not complete)

SELECT veslogg.latitude_dd AS lat_dd,
veslogg.longitude_dd AS long_dd,
veslogt.tripid,
vps_vessel.effcalc,
veslogg.gearcode,
veslogt.datelnd1,
to_char(veslogt.datelnd1, 'yyyy_MM') AS
step
FROM veslogs
INNER JOIN((veslogt
INNER JOIN vps_vessel ON veslogt.hullnum = vps_vessel.hull_id)
INNER JOIN veslogg ON veslogt.tripid = veslogg.tripid) ON veslogs.tripid = veslogt.tripid
WHERE((veslogs.sppcode) IN('POLL', 'COD', 'HADD', 'FLYT', 'FLGS', 'FLDAB', 'FLBB'))
AND((veslogg.gearcode) IN('OTC', 'OTF'))



My problem is twofold

That query returns numerous trips (TRIPID) with multiple effort (EFFCALC)

I need to get it returning one TRIPID with the Max EFFCALC.

I was assuming something like this would work.. .but it doesn’t


SELECT veslogg.latitude_dd AS lat_dd,
veslogg.longitude_dd AS long_dd,
veslogt.tripid,
max(vps_vessel.effcalc) as MaxEFF,
veslogg.gearcode,
veslogt.datelnd1,
to_char(veslogt.datelnd1, 'yyyy_MM') AS
step
FROM veslogs
INNER JOIN((veslogt
INNER JOIN vps_vessel ON veslogt.hullnum = vps_vessel.hull_id)
INNER JOIN veslogg ON veslogt.tripid = veslogg.tripid) ON veslogs.tripid = veslogt.tripid
WHERE((veslogs.sppcode) IN('POLL', 'COD', 'HADD', 'FLYT', 'FLGS', 'FLDAB', 'FLBB'))
AND((veslogg.gearcode) IN('OTC', 'OTF'))
Group by veslogt.tripid

Is this a having/where issue or am I just doing (or not doing) something stupid??

Thanks
-eric

 

[SantaMufasa]

I was assuming something like this would work.. .but it doesn’t

What error messages are you receiving?

What happens when you try this:

SELECT d.latitude_dd AS lat_dd,
       d.longitude_dd AS long_dd,
       b.tripid,
       max(c.effcalc) as MaxEFF,
       d.gearcode,
       b.datelnd1,
       to_char(b.datelnd1,'yyyy_MM') AS step
  FROM veslogs a, veslogt b, vps_vessel c, veslogg d
 WHERE b.hullnum = c.hull_id
   and b.tripid = d.tripid
   and a.tripid = b.tripid
   and (a.sppcode IN('POLL','COD','HADD','FLYT','FLGS','FLDAB','FLBB'))
   AND (d.gearcode) IN('OTC','OTF')) 
 Group by b.tripid,d.latitude_dd,d.longitude_dd,d.gearcode,b.datelnd1,
       to_char(b.datelnd1,'yyyy_MM');

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[efinnen]

In answer to your first question about why my last code snippet didn't work..

I got a "ORA-00979 Not a GROUP BY expression"

And I'm in the middle of playing with what you suggested . I initially got an error that it was "Not properly ended" but I think that was the second )) in

AND (d.gearcode) IN('OTC','OTF'))

Now it appears to be running and not completing its task (ran/running for ten minutes) or throwing an error.

I'm going to let it keep running and see if it completes anything...

 

[SantaMufasa]

Eric,

Your original error message ("ORA-00979 Not a GROUP BY expression") resulted from your having an aggregate expression ("max(c.effcalc)") in your SELECT (which makes the entire SELECT become an "aggregate SELECT"), but having 5 of your remaining 6 expression that you didn't mention in your GROUP BY clause. In an "aggregate SELECT", all expressions in the SELECT must either appear as an argument in an aggregate function or become an aggregate expression by mentioning it in your GROUP BY clause.

Re-try your original failed attempt while ensuring that all of your SELECTed expressions are aggregates (either as aggregate-function arguments or by listing them in your GROUP BY clause), and let us know the results.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[efinnen]

Mufasa

First off thanks for the advice, but I still seem to be chasing my tail here…

If I understand what you said earlier. (paraphrasing) Basically, we need to address every field that included in the select statement. Such that if we have a MAX(or whataver..) in the select statement. Then that field (s) does not get included in the group by. But ALL other fields from the select statement does..

I took your code from up above and removed the max and grouping to get a good “starting block” and to verify that it works fine (which it does)…

SELECT d.latitude_dd AS lat_dd,
       d.longitude_dd AS long_dd,
       b.tripid,
       c.effcalc,
       d.gearcode,
       b.datelnd1,
       to_char(b.datelnd1,'yyyy_MM') AS step
  FROM veslogs a, veslogt b, vps_vessel c, veslogg d
 WHERE b.hullnum = c.hull_id
   and b.tripid = d.tripid
   and a.tripid = b.tripid
   and (a.sppcode IN('POLL','COD','HADD','FLYT','FLGS','FLDAB','FLBB'))
   AND (d.gearcode IN('OTC','OTF'));

I also tried as an experiment (which failed) to run MAX-es on everything in the Select statement with nothing in the Group By..

But my rewrite of it (which is strikingly similar to yours) using the rules as I understand it from above is the following. And it has the same results, in that it doesn’t throw an error or produce any results. It just keeps running.

SELECT d.latitude_dd AS lat_dd,
       d.longitude_dd AS long_dd,
       b.tripid,
       MAX(c.effcalc) As EffMax,
       d.gearcode,
       b.datelnd1,
       to_char(b.datelnd1,'yyyy_MM') AS step
  FROM veslogs a, veslogt b, vps_vessel c, veslogg d
 WHERE b.hullnum = c.hull_id
   and b.tripid = d.tripid
   and a.tripid = b.tripid
   and (a.sppcode IN('POLL','COD','HADD','FLYT','FLGS','FLDAB','FLBB'))
   AND (d.gearcode IN('OTC','OTF'))
   Group By d.latitude_dd, d.longitude_dd, b.tripid, d.gearcode, b.datelnd1, to_char(b.datelnd1,'yyyy_MM');

Thanks for your time…


-e

 

[SantaMufasa]

Code that just runs and runs usually results from missing indexes. In your case, these columns should have indexes:

veslogs.tripid
veslogs.sppcode
veslogt.hullnum
veslogt.tripid
vps_vessel.hull_id
veslogg.tripid
veslogg.gearcode

Confirm that indexes exist on those columns, then re-run your query, posting the new results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[efinnen]

DING DING DING DING...

That would be the correct answer.. Thanks!

It works, but it is ridiculously slow, (but I'll deal with that later...)

I must say I don't quite understand everything to do with indexes, but three of those fields did not have any indexes associated with them.

Thanks Again
-e

 

[SantaMufasa]

I don't quite understand everything to do with indexes

To assist in your quest to understand more about indexes, think of them in this fashion:

I give you the assignment to look up information in the 25-volume Encyclopedia Britannica, concerning the "U.S. Naval Academy" in Annapolis, MD. But, as part of my specifications, I require you to read all information in all 25 volumes to ensure that you miss no information concerning the "U.S. Naval Academy" that may appear in other entries in the encyclopedia.

Let's also assume that I want you to compare the information you found in the Britannica with information re: the Academy that appears in World Book, Oxford, and Wikipedia laboring under the same restrictions as before: You must read all entries in each of those publications so ensure that you don't miss any entries re the Academy.

You can certainly imagine how long that would take. Imagine how much faster it would be if I remove the "read-it-all" (i.e., full-table-scan) restriction, allowing you to take advantage of the indexing inherent in the encyclopedias. The time/performance difference would be monumental.

Those were precisely the unacceptable performance challenges that you were encountering when you were without indexes on columns that you referenced in your WHERE clauses.

Does this fill in a few of the gaps re: indexes?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[efinnen]

Thanks for the clarification. It helps quite a bit! I still have a lot of learnin' left to do with this Oracle stuff.

-e