Hi,
I have a query that's using an implicit join. I would like to group the results by JOB TYPE only, listing the associated LOCATION and NETWORK number, and COST sum based on JOB TYPE. In order to get results without error, I have to add c.afenumber and a.wellname to the GROUP BY clause. I only want to group by b.wvtyp, since I'd like to see the duplicates for LOCATION. For example, I'd like to see:
LOCATION NETWORK COST JOB TYPE
11111 net1 30000 jobtype1
11111 net2 50000 jobtype2
There are typically more than one Network numbers associated to a single location, which is why I'd like to see the Location duplicates. If I group by LOCATION, NETWORK, and JOB TYPE, I get the following:
LOCATION NETWORK COST JOB TYPE
11111 net1 30000 jobtype1
22222 net2 50000 jobtype1
44444 net6 20000 jobtype3
It will only show me one NETWORK number for each LOCATION, since the LOCATION doesn't display the duplicates.
I hope I'm making sense, since I'm pretty new to SQL. Here is the code (without the working group by):
SELECT
SUBSTR(a.wellname,1,30) "LOCATION",
SUBSTR(c.afenumber,1,15) "NETWORK",
SUM(e.cost) as COST,
b.wvtyp "JOB TYPE"
FROM
wvwellheader a,
wvjob b,
wvjobafe c,
wvjobreportcostgen e
WHERE
a.idwell = b.idwell
AND b.idwell = c.idwell
AND c.idwell = e.idwell
AND b.idrec = c.idrecparent
AND c.afenumber IS NOT NULL
AND b.DTTMSTART > to_date( '01/JUN/2008', 'DD/MON/YYYY')
AND c.syscreatedate =
( SELECT MAX(f.syscreatedate)
FROM wvjobafe f
WHERE f.idwell = c.idwell)
GROUP BY
b.wvtyp
ORDER BY
a.wellname
I hope I've provided some clarity in regards to what I'm trying to accomplish
Thanks!
Start Free Trial
