Use the same operation set row by row on two table subqueries

Question

The problem is as follows. We have two table subqueries (A and B). I need to apply the formula
a.Processed / b.Total * 100 row by row and the result to be displayed in a new column “Activity %”

I tried the following query:

SELECT
    (
        a.Processed / b.Total * 100
    ) AS "Activity %"
FROM
    (
        SELECT
            sla_date,
            request_type,
            CAST(
                SUM(sla_value) AS DECIMAL(
                    10,
                    2
                )
            ) AS Processed
        FROM
            table_schema.table_name
        WHERE
            team = '100'
            AND sla_duration_hrs < 480
        GROUP BY
            sla_date,
            request_type
    ) AS A 
    JOIN(
        SELECT
            sla_date,
            request_type,
            CAST(
                SUM(sla_value) AS DECIMAL(
                    10,
                    2
                )
            ) AS Total
        FROM
            table_schema.table_name
        WHERE
            team = '100'
        GROUP BY
            sla_date,
            request_type
    ) AS B
        ON a.sla_date = b.sla_date
    AND a.request_type = b.request_type  

but I receive the error

"SQL Error [SE001]: Splice Engine exception: Problem invoking ISpliceVisitor visit method for class.splicemachine.db.impl.sql.compile.FromBaseTable"

I could use UNION ALL for this task, but the real table subqueries have hundreds of rows and the solution is far from being elegant and time saving.

Any idea on how to rework the query?

Thank you.

Answer 1

Please try the following rewrite and see if it works around the error:

SELECT
    sla_date,
    request_type,
    CAST(SUM(case when sla_duration_hrs < 480 then sla_value else 0 end) 
         AS DECIMAL(10,2)) /
    CAST(SUM(sla_value) AS DECIMAL(10,2)) AS  "Activity %"
FROM
    table_schema.table_name
WHERE
    team = '100'
GROUP BY
    sla_date,
    request_type;

Answer 2

It’s essentially trying to compute the % that sums of sla_value for the rows having sla_duration_hrs less than 480 over total sum of sla_value for the team ‘100’. It involves a self join. Please look at the example here http://www.1keydata.com/sql/sql-percent-to-total.html to get some ideas.