CODEWITHSUNDEEP
sqloracle-database
Madmartigan
Madmartigan

Reputation: 453

ORACLE SQL QUERY union

I have the following table:

site||kw||date
M   ||50||1-1-2013
A   ||60||1-1-2013
...

I need to show, filtering between two dates

Site||avg(kw)||MAX_KW_PERIOD||MAX_KW_TOTAL

being MAX_KW_PERIOD the maximum value of kw between the two dates and MAX_KW_TOTAL the maximum historical value of kw.

I try with a union query:

SELECT Site,
       avg(TM.kw) "AVG_KW", 
       MAX(TM.kwGen) "MAX KW PERIOD",
       TO_NUMBER(NULL) "MAX_TOTAL"
  FROM TABLE
 WHERE DATE BETWEEN DATE1 
                AND DATE2
GROUP BY 
       Site
UNION
SELECT Site,
       TO_NUMBER(NULL)"AVG_kW",
       TO_NUMBER(NULL)"MAX KW PERIOD",
       MAX(TM.kwGEN) "MAX TOTAL" 
  FROM TABLE
GROUP BY 
       Site

but i obtain:

SITE||AVG_KW||MAX_KW PERIOD||MAX TOTAL
   A||100   ||110          ||(null)
   A||(null)||(null)       ||160

How can i do to obtain only one row for site like

SITE||AVG_KW||MAX_KW PERIOD||MAX TOTAL
   A||100   ||110          ||160

thanks a lot.

Upvotes: 0

Views: 83

Answers (1)

shree.pat18
shree.pat18

Reputation: 21757

Try this:

with maxkw as
((SELECT site, MAX(kw) maxtotal FROM tbl t GROUP BY SITE)),

avgkw as
(SELECT t.Site,
        avg(kw) "AVG_KW", 
        MAX(kw) "MAX_KW_PERIOD"--,
 FROM tbl t 
 WHERE DATE BETWEEN '20130101' AND '20130104'
 GROUP BY t.Site)

 select t.site, t.avg_kw, t.max_kw_period, c.maxtotal
 from avgkw t
 inner join maxkw c on t.site = c.site

Upvotes: 1

Related Questions