0

I have two different SQL Query as below, I want to calculate the value from these two different SQL query from different tables.

-- query A（table_name_a）, `date` format is StringField '20191007' 
SELECT code,
          sum(CASE WHEN remark IN ('111', '222', '333')
                    THEN t.value ELSE 0
                    END)/
          sum(CASE WHEN remark IN ('444')
                    THEN t.value ELSE 0
                    END) AS val
        FROM table_name_a
       WHERE code IN ('%(code)s')
         AND date BETWEEN '%(start_date)s' AND '%(end_date)s'
       GROUP BY code, date

-- query B(table_name_b), `date` format is DateField '2019-10-07 00:00:00' 
SELECT code,
          sum(CASE WHEN remark IN ('111', '222', '333')
                    THEN t.value ELSE 0
                    END)/
          sum(CASE WHEN remark IN ('444')
                    THEN t.value ELSE 0
                    END) AS val
       FROM table_name_b
       WHERE code IN ('%(code)s')
         AND substr(regexp_replace(date,'-',''),1,8) BETWEEN '%(start_date)s' AND '%(end_date)s'
       GROUP BY code, substr(regexp_replace(date,'-',''),1,8)

If I input parameter '%(code)s', '%(start_date)s' and %(end_date)s' with '00001' , '20191001' and '20191030', I got result as below

    code    val
1   00001   0.00798752 -- result from query A

    code    val
1   00001   0.00472937 -- result from query B

Now I would like to get the result by 0.00798752 + 0.00472937, I have four value from four different SQL query, can I add them together via only one SQL query?

Improve this question
6
  • how about a single UNION query and then SUM the result? Commented Nov 7, 2019 at 9:29
  • @ADyson Hi, thanks, what about subtraction, I also need subtraction the value Commented Nov 8, 2019 at 0:57
  • You didn't mention that previously. If you have a new problem, ask a new question Commented Nov 8, 2019 at 1:04
  • @HAdes Hi, I saw you problem in stackoverflow.com/questions/280494/… may I know your final solution? Commented Nov 8, 2019 at 1:50
  • 1
    @ADyson Hi, I asked a new question stackoverflow.com/questions/58759391/… , do you know how to minus the results, thank you so much for any advice, if I use val1-val2-val3-val4, I got an error of AnalysisException: Could not resolve column/field reference: 'val2', I was stuck in this problem......... Commented Nov 8, 2019 at 2:37

1 Answer 1

Reset to default
1

ADyson was right... you can use union to get one resultset with four records instead of four resultsets with one row. In addition you need to sum your value and group by code. An example for that could be the following snippet: 

SELECT code, SUM(val)
FROM (
-- query A（table_name_a）, `date` format is StringField '20191007' 
SELECT code,
          sum(CASE WHEN remark IN ('111', '222', '333')
                    THEN t.value ELSE 0
                    END)/
          sum(CASE WHEN remark IN ('444')
                    THEN t.value ELSE 0
                    END) AS val
        FROM table_name_a
       WHERE code IN ('%(code)s')
         AND date BETWEEN '%(start_date)s' AND '%(end_date)s'
       GROUP BY code, date
UNION
-- query B(table_name_b), `date` format is DateField '2019-10-07 00:00:00' 
SELECT code,
          sum(CASE WHEN remark IN ('111', '222', '333')
                    THEN t.value ELSE 0
                    END)/
          sum(CASE WHEN remark IN ('444')
                    THEN t.value ELSE 0
                    END) AS val
       FROM table_name_b
       WHERE code IN ('%(code)s')
         AND substr(regexp_replace(date,'-',''),1,8) BETWEEN '%(start_date)s' AND '%(end_date)s'
       GROUP BY code, substr(regexp_replace(date,'-',''),1,8)
) res
GROUP BY CODE;

EDIT1: For substracting instead of sum you can set your querys as sources in from-clause. I used CTEs for the two queries. Example:

WITH q1 as (
SELECT code,
          sum(CASE WHEN remark IN ('111', '222', '333')
                    THEN t.value ELSE 0
                    END)/
          sum(CASE WHEN remark IN ('444')
                    THEN t.value ELSE 0
                    END) AS val
       FROM table_name_a
       WHERE code IN ('%(code)s')
       AND date BETWEEN '%(start_date)s' AND '%(end_date)s'
       GROUP BY code, date
),q2 as (
SELECT code,
          sum(CASE WHEN remark IN ('111', '222', '333')
                    THEN t.value ELSE 0
                    END)/
          sum(CASE WHEN remark IN ('444')
                    THEN t.value ELSE 0
                    END) AS val
       FROM table_name_b
       WHERE code IN ('%(code)s')
       AND substr(regexp_replace(date,'-',''),1,8) BETWEEN '%(start_date)s' 
       AND '%(end_date)s'
       GROUP BY code, substr(regexp_replace(date,'-',''),1,8)
) 
select
    q1.code
    , q1.val-q2.val as subtracted_val
from q1
inner join q2
on q1.code=q2.code
;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hi, thanks, what about subtraction, I also need subtraction the value 😂
In that case you should use your selects as source in the from clause and join them with code. Then you will get one row for each code with four different values from each select statement. Take a look at my edited answer above :)
Hi, thanks so much for the edited answer, I also had another solution for this problem, just let the value *-1 that you want to minus, make the value negative then sum them up, hahaha, it's amazing, stackoverflow.com/questions/58759391/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.