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?