SQL Query bad results

Posted on 2014-08-15
Last Modified: 2014-08-15
Hi experts, I need to solve the results of follows query:
SELECT utchora
           , pointnumber
           , siteid
           , MAX (DECODE (rn, 1, valor_inst)) valor_inst
           , MAX (DECODE (rn, 1, tlq_inst)) tlq_inst
           , AVG (valor_inst) valor_prom
           , case when max(bitand(tlq_inst, 168)) = 0                 
             max(tlq_inst) keep (dense_rank first order by utctime)  
             else 8
             end as tlq_prom
           , MAX (DECODE (max0, 1, valor_inst)) valor_max
           , MAX (DECODE (max0, 1, tlq_inst)) tlq_max
           , MAX (DECODE (max0, 1, utctime)) utctime_max
           , MAX (DECODE (min0, 1, valor_inst)) valor_min
           , MAX (DECODE (min0, 1, tlq_inst)) tlq_min
           , MAX (DECODE (min0, 1, utctime)) utctime_min
        FROM (
        SELECT TRUNC ( utctime, 'HH24')  utchora 
             , utctime 
             , pointnumber
             , siteid
             , valor_inst
             , tlq_inst
             , valor_prom
             , tlq_prom
             , valor_max
             , tlq_max
             , utctime_max
             , valor_min
             , tlq_min
             , utctime_min
             , ROW_NUMBER () OVER (PARTITION BY pointnumber, TRUNC ( utctime, 'HH24') /*+ 1/24*/ ORDER BY utctime /*DESC*/) rn
             , AVG (valor_inst) OVER (PARTITION BY pointnumber ORDER BY utctime DESC) av0
             , ROW_NUMBER () OVER (PARTITION BY pointnumber, TRUNC ( utctime, 'HH24') /*+ 1/24*/ ORDER BY valor_inst DESC) max0
             , ROW_NUMBER () OVER (PARTITION BY pointnumber, TRUNC ( utctime, 'HH24') /*+ 1/24*/ ORDER BY valor_inst) min0
        FROM a_5min_023
       WHERE TRUNC ( utctime, 'HH24') >= to_date('11/08/2014 03:00:00','dd/mm/yyyy hh24:mi:ss') 
       AND TRUNC ( utctime, 'HH24') < to_date('11/08/2014 04:00:00','dd/mm/yyyy hh24:mi:ss') 
       AND POINTNUMBER = 230001
       GROUP BY pointnumber, utchora, siteid ;

Open in new window

11/08/2014 03:00:00 a.m.|230001||0|32|0|8||||0|32|11/08/2014 03:00:00 a.m.
11/08/2014 03:00:00 a.m.|230001|0|||6|8|11|8|11/08/2014 03:55:00 a.m.|||

Open in new window

The problems are:
1 - Returns 2(two) rows with results. I need to get just one row.
2 - I need obtain the average of the segment with:
 AVG (valor_inst) valor_prom 

Open in new window

But never is calculated.
Could you help me to found the solutions?
All the scripts, attached.
Thankyou in advanced
Question by:carlino70
    LVL 73

    Expert Comment

    looks like your data above is just the current results, I can get those by running the query you posted.

    What are the results you WANT?

    Author Comment

    ok. I want obtain:
    11/08/2014 03:00:00 a.m.|230001     |0     |0         |32      |6         |8       |11       |8      |11/08/2014 03:55:00 a.m.|0        |32     |11/08/2014 03:00:00 a.m.

    Open in new window

    The valor_prom must be AVG(valor_inst), but not is calculated.
    LVL 73

    Expert Comment

    I don't understand -  your expected data and your description don't seem to match.

    How can VALOR_PROM be 6 if it's the AVG(valor_inst) which is 0 and null.  Nulls are skipped in averages, so it's just AVG(0) which is 0,  not 6
    LVL 73

    Accepted Solution

    also, one of your sideids is null, so, grouping by siteid with null in one value and 0 in all the rest will always result in 2 rows.

    Are all of your insert statements correct?  Maybe that null shouldn't be there

    Author Comment

    Yes Sr., the problem are the null values for siteid.

    For the AVG(valor_inst) now is correct, because is calculated with 12 registers, given by the utctime segment chosen.

    I appreciate your help, it was very useful to me


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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.

    Suggested Solutions

    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…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    759 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

    13 Experts available now in Live!

    Get 1:1 Help Now