sql query how to calculate values use same column in one table?

0
votes

I would like to calculate the same column called value in one table called table_name:

CREATE TABLE IF NOT EXISTS Table_Name(
Code VARCHAR(20)          COMMENT 'Code',
Date                      COMMENT 'Date',
Value DECIMAL(25,2)         COMMENT 'Value',
Remark STRING                 COMMENT 'Value type, "111, 222, or 333 etc"',

supposed I need to calculate "(value's type = 111 or 222 or 333)/(value's type = 444)" between 20180201 and 20180228, and I use SQL query as below:

select t.code,t.date,t.remark,t1.value/t2.value as val
from table_name t
    right join (
        select sum(t.value) fzqsz, t.code from table_name t
        WHERE remark = '111' OR remark = '222' OR remark = '333'
        group by t.code,t.date
    ) t1 on t.code = t1.code
    left join (
        select sum(t.value) value,t.code from table_name t
        WHERE remark = '444'
        group by t.code,t.date
    ) t2 on t1.code = t2.code
    -- if I put below line in `SQL query` I get nothing(0 results)
    -- where t.code='00001' and t.date >='20180201' and date <= '20180228'

I only select remark="111 or 222 or 333", I don't know why all remarks("555 and 666") appeared, I got results as below:

    code         date              remark                  val
1   00001   2018-02-25 00:00:00.0    111                0.00002929
2   00001   2018-02-25 00:00:00.0    222                0.00002977
3   00002   2018-02-25 00:00:00.0    333                0.00002917
4   00003   2018-02-25 00:00:00.0    444                0.00002987
3   00001   2018-02-25 00:00:00.0    555                0.00002917
4   00002   2018-02-25 00:00:00.0    666                0.00002987

Is the SQL query right? I'm sure there is some problem with my SQL query. Really appreciated with any advice.

3
sqloracle
I don't have your data to test, but - outer joins smell like culprit. – Littlefoot
Are you sure you are using Oracle? Your CREATE TABLE is is invalid in Oracle. – a_horse_with_no_name
You group by code and date, but only select the code in your sub queries. Thus you don't know what the values you are selecting actually refer to. I guess you just want one t1 sum and one t2 sum per t, but for what dates? For the t date? Or the whole date range given? Or regardless of the date? – Thorsten Kettner
By the way: I would refrain from right outer joins. They are very hard to read. Mixing them with left outer joins even are a horrible thing to do in my opinion. I must admit that I would have to try the query to be sure what it does. It seems t1 is the main data source in your query and you only add t and t2 where you find matches. But shouldn't t be the main table where t1 and t2 are tried to be joined? – Thorsten Kettner
As to I don't know why all remarks("555 and 666") appeared. They do, because there is no restriction. You are selecting all t with t.code='00001' and t.date >='20180201' and date <= '20180228'. What surprises me hence is why there are code '00002' and '00003' in your results. This is weird and shouldn't be possible with the query you are showing. – Thorsten Kettner

3 Answers

1
votes

use inner join and mutiple or can replae by in

select t.code,t.date,t.remark,t1.value/t2.value as val
from table_name t
     join (
        select sum(t.value) fzqsz, t.code from table_name t
        WHERE remark in( '111','222' ,'333')
        group by t.code,t.date
    ) t1 on t.code = t1.code
     join (
        select sum(t.value) value,t.code from table_name t
        WHERE remark = '444'
        group by t.code,t.date
    ) t2 on t1.code = t2.code 
    where t.code='00001' and t.date >='20180201' and date <= '20180228'

you could do below using case when

 select (sum(case when remark in( 111,222 ,333) then t.value else 0 end)/
 sum(case when remark=444 then t.value else 0 end)) as val,
 t.code,t.date from table_name t
 where t.code='00001' and t.date >='20180201' and date <= '20180228'
  group by t.code,t.date
0
votes

you can use select case rather than joining same tables.

select t2.code, t2.date, t2.remark, t3.val/t3.val2 from 
table_name t2
inner join (
    select t.code, sum(case when t.remark in ('111', '222', '333') then 1 else 0 end) as val
        , sum(case when t.remark in ('444') then 1 else 0 end) as val2
    from table_name t
    where t.code = '00001' and t.date between '20180201' and '20180228'
    group by t.code) t3 on t3.code = t2.code
where t2.remark in ('111', '222', '333', '444')
0
votes

READ the comments!

Elsa, it makes little sense that you are only reacting to the answers. The two posters are merely guessing what you may want to achieve. There are questions right under your request, you have so far failed to react to. Is it really Oracle you are using? What is the table's key (i.e. what identifies one row uniquely)? Why do you group by date inside the sub queries? What result rows do you expect?

As you don't seem to be reading the comments, I have posted this as an answer.