Use data from one query in another

Queries and Resources
1

Hi,
I need to use SUM of one column in query5 from query6 so i did:

 (
   SUM(planned_bottles) / (SUM(planned_bottles) 
      + (SELECT SUM(planned_bottles)
        FROM {{query6.query}} )))                  
                           AS percent_of_plan,

unfortunately im getting error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))) AS percent_of_plan

Can not find anything in documentation

query6 is exactly same as query5 but other way round (need data from query5 into query6)

Is there any way to get data from query6 into query5 ?

2

Welcome to the forum! Can you post both queries?

3

Hi.
Of course.

query5:

SELECT
  filling_date,
  SUM(planned_filling_time) as planned_time,
  SUM(filling_time) as real_time,
  (SUM(planned_filling_time)-SUM(filling_time)) as downtime,
  AVG(line_run_rate) as runrate,
  ((SUM(filled_bottles) / SUM(filling_time)) * 60) as achieved,
   (
   SUM(planned_bottles) / (SUM(planned_bottles) 
      + (SELECT SUM(planned_bottles)
        FROM {{query6.query}} )))                  
                           AS percent_of_plan,                 
  line_availability,
  SUM(filled_bottles) as filled_bott,
  SUM(planned_bottles) as planned_bott,
  SUM(packed_bottles) as packed_bott,
  (SUM(filled_bottles)-SUM(packed_bottles)) as defects
FROM
  {{date3.formattedValue}}
WHERE
  filling_date <> '0000-00-00'
  AND line_number = '1'
  AND filling_date BETWEEN '{{date1.value}}' AND '{{date2.value}}'
GROUP BY
  filling_date
ORDER BY
  filling_date

query6:

SELECT
  filling_date,
  SUM(planned_filling_time) as planned_time,
  SUM(filling_time) as real_time,
  (SUM(planned_filling_time)-SUM(filling_time)) as downtime,
  AVG(line_run_rate) as runrate,
  ((SUM(filled_bottles) / SUM(filling_time)) * 60) as achieved,
   (
   SUM(planned_bottles) / (SUM(planned_bottles) 
      + (SELECT SUM(planned_bottles)
        FROM {{query5.query}} )))                  
                           AS percent_of_plan,                 
  line_availability,
  SUM(filled_bottles) as filled_bott,
  SUM(planned_bottles) as planned_bott,
  SUM(packed_bottles) as packed_bott,
  (SUM(filled_bottles)-SUM(packed_bottles)) as defects
FROM
  {{date3.formattedValue}}
WHERE
  filling_date <> '0000-00-00'
  AND line_number = '1'
  AND filling_date BETWEEN '{{date1.value}}' AND '{{date2.value}}'
GROUP BY
  filling_date
ORDER BY
  filling_date

I have two production lines. Line 1 and Line 2. To calculate percent of plan for each line, each production day (100% is plan for both lines, sometimes there is more than one product on one line in one day) i need: for line 1: sum of planned bottles line 1 / (sum of planned bottles line 1 + sum of planned bottles line 2)
and for line 2: sum of planned bottles line 2 / (sum of planned bottles line 2 + sum of planned bottles line 1)

I am reading production from each line to two separate tables by date between two date inputs. but to calculate some data for line 1 i need some data from line 2 and other way round.

I hope that i explained this good enough.

4

a couple of things:
you have query6.query instead of query6.data
but the one thing that might need to change is that you have each query referring to the other, so which one goes first?
I would suggest slowly building the results for each query until the error is gone and also you have single quotes around the date values which you should not need:
'{{date1.value}}' AND '{{date2.value}}'

1 Like