How to output results in the same row, in a separate column, but from the same table based on date

Hello, I have the following query that pulls average subcool values for specific wells, grouped by YY-Mon. The query works great, but what I would like to do, is have an additional column that pulls average subcool values for a different set of specific wells, also grouped by the SAME YY-Mon. So far I have only been able to show the results on seperate rows for each month. I would like both subcool values to be displayed on the same row.

So output should look like this:

Production Month | Min Subcool Target | Max Subcool Target | Pad 101 Subcool Avg | PAD 102 SUBCOOL AVG (this is the additional column that I would like to see, with data populated from the second query)

SELECT
   to_char(P1."DATE_OF_PRODUCTION",   'yy-Mon') AS "Production Month",
   B2."MIN_VALUE" AS "Min Subcool Target",
   B2."MAX_VALUE" AS "Max Subcool Target",
   avg(P1."SUBCOOL_RES") AS "Pad 101 Subcool Avg"
   
FROM
   "OFM"."PRD" P1,
   "OFM"."BOD_PARAMETERS" B2,
   "OFM"."BOD_XY_REF" B3
   
WHERE
   (B2."PARAM_ID" = 2516)
   AND (B3."WELL" = P1."WELL")
   AND (P1."WELL"  IN (SELECT well from tag_mapping WHERE tag_id LIKE '22P%.RESERVOIR.RAW' AND tag_set like 'PROD%'))

GROUP BY
   to_char(P1."DATE_OF_PRODUCTION",
   'yy-Mon'),
   B2."MIN_VALUE",
   B2."MAX_VALUE"

Here is the query that pulls the other set of specific wells.

SELECT
   to_char(P1."DATE_OF_PRODUCTION",   'yy-Mon') AS "Production Month",
   B2."MIN_VALUE" AS "Min Subcool Target",
   B2."MAX_VALUE" AS "Max Subcool Target",
   avg(P1."SUBCOOL_RES") AS "Pad 102 Subcool Avg"
   
FROM
   "OFM"."PRD" P1,
   "OFM"."BOD_PARAMETERS" B2,
   "OFM"."BOD_XY_REF" B3
   
WHERE
   (B2."PARAM_ID" = 2516)
   AND (B3."WELL" = P1."WELL")
   AND (P1."WELL"  IN (SELECT well from tag_mapping WHERE tag_id LIKE '21P%.RESERVOIR.RAW' AND tag_set like 'PROD%'))

GROUP BY
   to_char(P1."DATE_OF_PRODUCTION",
   'yy-Mon'),
   B2."MIN_VALUE",
   B2."MAX_VALUE"
mskittenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
you can try something like this...

SELECT   TO_CHAR(p1.date_of_production, 'yy-Mon') AS "Production Month",
         b2.min_value AS "Min Subcool Target",
         b2.max_value AS "Max Subcool Target",
         AVG(CASE
                 WHEN p1.well IN (SELECT well
                                    FROM tag_mapping
                                   WHERE tag_id LIKE '22P%.RESERVOIR.RAW' AND tag_set LIKE 'PROD%')
                 THEN
                     p1.subcool_res
             END)
             AS "Pad 101 Subcool Avg",
         AVG(CASE
                 WHEN p1.well IN (SELECT well
                                    FROM tag_mapping
                                   WHERE tag_id LIKE '21P%.RESERVOIR.RAW' AND tag_set LIKE 'PROD%')
                 THEN
                     p1.subcool_res
             END)
             AS "Pad 102 Subcool Avg"
    FROM ofm.prd p1, ofm.bod_parameters b2, ofm.bod_xy_ref b3
   WHERE (b2.param_id = 2516) AND (b3.well = p1.well)
GROUP BY TO_CHAR(p1.date_of_production, 'yy-Mon'), b2.min_value, b2.max_value;
0
sdstuberCommented:
that's probably not the most efficient way to do it, but I don't know your data distributions to determine what the most effective filtering method might be.

so use that more as an illustration of the technique, not necessarily the best-final solution.


that is,  use CASE inside the AVG to filter which set of data you need
0
Mark GeerlingsDatabase AdministratorCommented:
SQL does not support getting results from two different "where" clauses included in the results for one single-level query.  What you can do though is use either: a PL\SQL function or a nested "select" to return the results for the second "Subcool Avg" column.  But because you need a "group by" in your main query, you will also likely need to include the PL\SQL function or the nested "select" in the "group by" clause.  This could get complex, or it may cause a significant performance impact.

Another option would be two create two views, one using each of your two current queries.  Then, your report could do a simple join between the two views to get these two columns (plus the three common columns) in a single result set.  Depending on your data, it may be necessary to use outer joins rather than standard (inner) joins, if either set of rows is incomplete.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
one possible change, albeit at the cost of a 3rd hit to the tag_mapping table would be to combine both in clauses to act as a filter.

again, depending on your data volume/distribution and indexes, this may or may not be an improvement, you'll have to test to see


SELECT   TO_CHAR(p1.date_of_production, 'yy-Mon') AS "Production Month",
         b2.min_value AS "Min Subcool Target",
         b2.max_value AS "Max Subcool Target",
         AVG(CASE
                 WHEN p1.well IN (SELECT well
                                    FROM tag_mapping
                                   WHERE tag_id LIKE '22P%.RESERVOIR.RAW' AND tag_set LIKE 'PROD%')
                 THEN
                     p1.subcool_res
             END)
             AS "Pad 101 Subcool Avg",
         AVG(CASE
                 WHEN p1.well IN (SELECT well
                                    FROM tag_mapping
                                   WHERE tag_id LIKE '21P%.RESERVOIR.RAW' AND tag_set LIKE 'PROD%')
                 THEN
                     p1.subcool_res
             END)
             AS "Pad 102 Subcool Avg"
    FROM ofm.prd p1, ofm.bod_parameters b2, ofm.bod_xy_ref b3
   WHERE     (b2.param_id = 2516)
         AND (b3.well = p1.well)
         AND p1.well IN
                 (SELECT well
                    FROM tag_mapping
                   WHERE     (tag_id LIKE '21P%.RESERVOIR.RAW' OR tag_id LIKE '22P%.RESERVOIR.RAW')
                         AND tag_set LIKE 'PROD%')
GROUP BY TO_CHAR(p1.date_of_production, 'yy-Mon'), b2.min_value, b2.max_value;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mskittenAuthor Commented:
This worked great. Thank you! One quick question, how do I sort the date so that it's in proper order? ie 07-Jan 07-Feb 07-Mar, etc
0
sdstuberCommented:
don't convert it to a string, leave it a date but truncated to the month

or, group by both the date value and the string value


SELECT   trunc(p1.date_of_production,'mm') month, TO_CHAR(p1.date_of_production, 'yy-Mon') AS "Production Month",
...
GROUP BY trunc(p1.date_of_production,'mm'),TO_CHAR(p1.date_of_production, 'yy-Mon'), b2.min_value, b2.max_value;
order by month
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.