[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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"
0
mskitten
Asked:
mskitten
  • 4
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now