Query result as variable in another using jdbc

Question

Because I want to optimize a query, I want to rennounce at a join. Due of that, I need to declare a variable before the main query, but I can't find a solution to use it in jdbc statement.

Original query:

SELECT 
  d.orders
  SUM(price * qty) / d.orders
FROM main_table
INNER JOIN (
   SELECT SUM(qty) AS orders FROM main_table
   WHERE status = 1) d
WHERE status = 1
GROUP BY d.orders

formatted query:

SET @orders = SELECT SUM(qty) AS orders FROM main_table WHERE status = 1
SELECT 
  @orders,
  SUM(price * qty) / @orders
FROM main_table
WHERE status = 1

I can't find a solution to execute correctly this formatted query using jdbc. Due of grouping by a variable, I'm not sure this will run correctly. Also, I don't want to split this in 2 separated executions using the results of first in the second because will increase the execution time and queries count.

A part of jdbc code

val statement = conn.prepareStatement(query)
val rset = statement.executeQuery()
if (rset.next()) {
   // read results
}

This is executed every 10 seconds because is used in a realtime dashboard. The db type is Impala Kudu (I'm thinking to build the queries as stored procedures, but I'm afraid that Kudu doesn't have support for it). The app is writed in Scala but use jdbc from Java to querying the database.

I already removed some methods from query (decimal casts) to optimize the query and leave it as simple as possible but I still want to remove some unusefull joins there. Is not the only, I have some other similar queries there, so, a small upgrade can have a huge benefit.

thanks

Answer 1

I suggest to use

SELECT 
  SUM(qty),
  SUM(price * qty)
FROM main_table
WHERE status = 1

When you get result just divide second value by first value in you java code.

Or even better:

SELECT 
  SUM(price * qty) / SUM(qty)
FROM main_table
WHERE status = 1