SQL Query bad results

carlino70
carlino70 used Ask the Experts™
on
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                 
               then                                                    
             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

Results:
UTCHORA|POINTNUMBER|SITEID|VALOR_INST|TLQ_INST|VALOR_PROM|TLQ_PROM|VALOR_MAX|TLQ_MAX|UTCTIME_MAX|VALOR_MIN|TLQ_MIN|UTCTIME_MIN
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
Regards
table-023.sql
inserts.sql
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

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

Commented:
ok. I want obtain:
UTCHORA                 |POINTNUMBER|SITEID|VALOR_INST|TLQ_INST|VALOR_PROM|TLQ_PROM|VALOR_MAX|TLQ_MAX|UTCTIME_MAX             |VALOR_MIN|TLQ_MIN|UTCTIME_MIN             
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.
Thankyou
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012
Commented:
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

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

Thankyou

Author

Commented:
Excellent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial