0

I want the Min Price for purpose-A items and Max price for purpose-B items, moreover I group my items by zone.

SELECT ZONE, MIN_PRICE, MAX_PRICE --,LEFT_ZONE
  FROM 
  (SELECT MIN(PRICE) AS MIN_PRICE , ZONE  AS LEFT_ZONE
    FROM MYTABLE 
    WHERE PURPOSE = 'A'
      AND SOMETHING = 'SOMEVALUE'
    GROUP BY ZONE 
   )
    FULL OUTER JOIN 
  (SELECT MAX(PRICE) AS MAX_PRICE, ZONE_CD  
    FROM MYTABLE 
    WHERE PURPOSE = 'B'
      AND SOMETHING = 'SOMEVALUE'
    GROUP BY ZONE 
   )
  ON LEFT_ZONE = ZONE 

This query gives the output I want, but I don't like it for two reasons:

1) I want

FROM MYTABLE 
WHERE SOMETHING = 'SOMEVALUE'

to be called only once.

2) I get ZONE null when the row comes from the right table in my full outer join.

How could I fix these problems.

Are there some more issues in my query?

jimifiki
  • 5,377
  • 2
  • 34
  • 60
  • What is the reason for collecting the Min_Price of purpose 'A' with the max_price of purpose 'B'? – Pieter Geerkens Mar 01 '13 at 13:56
  • In a given zone you can sell today at the lowest bid price traded yesterday and buy at the highest ask price traded yesterday. I want the output of my query to contain "zone, today_sell_price, today_buy_price". Is that meaningfull? – jimifiki Mar 01 '13 at 14:08

2 Answers2

3

Have you tried using a CASE expression to get this:

select zone,
  min(case when PURPOSE = 'A' then price end) min_price,
  max(case when PURPOSE = 'B' then price end) max_price
from MYTABLE 
where SOMETHING = 'SOMEVALUE'
group by zone
Taryn
  • 242,637
  • 56
  • 362
  • 405
0

Try this:

  SELECT 
    ZONE, 
    SUM (CASE WHEN PURPOSE = 'A' THEN MIN(PRICE) ELSE 0 END) AS MIN_PRICE,
    SUM (CASE WHEN PURPOSE = 'B' THEN MAX(PRICE) ELSE 0 END) AS MAX_PRICE
  FROM 
    MYTABLE 
  WHERE 
    SOMETHING = 'SOMEVALUE'
  GROUP BY 
    ZONE 

or any small variation of this

Retired_User
  • 1,595
  • 11
  • 17