?
Solved

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

Posted on 2012-03-26
6
Medium Priority
?
365 Views
Last Modified: 2012-03-26
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
Comment
Question by:mskitten
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37767474
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37767494
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 37767516
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
Independent Software Vendors: 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!

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37767564
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
 

Author Closing Comment

by:mskitten
ID: 37768734
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37768854
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question