CODEWITHSUNDEEP
sqloracle-databaseexpression
Paulo Magalhães
Paulo Magalhães

Reputation: 31

Query with equation

I have 3 queries that return 3 values. I'd Like to join the queries to perform the following expression:

(MgO + CaO)/SiO2

How can I do that?

MgO:

SELECT sampled_date, result 
FROM AF_VW WHERE element = 'MgO' AND ROWNUM = 1 ORDER BY sampled_date DESC;

CaO:

SELECT sampled_date, result 
FROM AF_VW WHERE element = 'CaO' AND ROWNUM = 1 ORDER BY sampled_date DESC;

SiO2:

SELECT sampled_date, result 
FROM AF_VW WHERE element = 'SiO2' AND ROWNUM = 1 ORDER BY sampled_date DESC;

Upvotes: 2

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

This is a bit long for a comment.

The queries in your question are probably not doing what you expect. Oracle evaluates the WHERE clause before the order by. So, the following chooses one arbitrary row with MgO and then does the trivial ordering of the one row by date:

SELECT sampled_date, result 
FROM AF_VW
WHERE element = 'MgO' AND ROWNUM = 1
ORDER BY sampled_date DESC;

Really, to get the equivalent result, you would need to emulate the same, unstable logic. Unstable, because the results are not guaranteed to be the same if the query is run multiple times:

with mg as (
          SELECT sampled_date, result 
          FROM AF_VW
          WHERE element = 'MgO' AND ROWNUM = 1
         ),
     coa as (
          SELECT sampled_date, result 
          FROM AF_VW
          WHERE element = 'CaO' AND ROWNUM = 1
         ),
     sio2 as (
          SELECT sampled_date, result 
          FROM AF_VW
          WHERE element = 'SiO2' AND ROWNUM = 1
     )
select (mgo.result + cao.result) / sio2.result
from mgo cross join cao cross join sio2;

I suspect you really want the most recent sample date, which is what VKP's answer provides. I just thought you should know that is not what your current queries are doing.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

with x as (
SELECT sampled_date, result, element,
row_number() over(partition by element order by sampled_date desc) rn 
FROM AF_VW)
, y as (
select 
case when element = 'MgO' then result end as MGO,
case when element = 'CaO' then result end as CaO,
case when element = 'SiO2' then result end as SiO2
FROM x where rn = 1)
select (mgo+cao)/sio2 from y;

You can use row_number function instead of rownum and then select the results for the 3 elements.

Upvotes: 2

Related Questions