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
359 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
  • 4
6 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now