Combine 4 SQL sub-query into Single query (ZOHO Analytics)

Question

ZOHO Analytics is a database which supports SQL syntax of MySQL, SQL server, Oracle, PostgreSQL, etc...

I'm using syntax of MySQL.

I am able to construct the whole SQL, it consists of 4 levels of sub-query, but here's the problem. ZOHO Analytics only allows 1 level of sub-query. So, I end up have to split the full SQL into 2 "View Table".

*Note: text wraps with double quote refers to table name or column name.

Here's the 1st View Table "Sales Person Commission Part 1":

select 
sp."name" as "Sales Person Name",
round(t1."gross sales", 2) as "Gross Sales",
if(t1."Gross Partner Profit" is null, 0.00, round(t1."Gross Partner Profit", 2)) as "Gross Partner Profit",
round(t1."Gross Sales" - if(t1."Gross Partner Profit" is null, 0, t1."Gross Partner Profit"), 2) as "Gross Profit",
round(100 * (t1."Gross Sales" - if(t1."Gross Partner Profit" is null, 0, t1."Gross Partner Profit")) / t1."gross sales", 2) as "Gross Profit Percentage"
from
(
select
i."sales person id",
sum(ii."total (bcy)") as "gross sales",
sum(item."partner price" * ii."quantity") as "Gross Partner Profit"
from
"invoice items (zoho finance)" ii 
inner join "invoices (zoho finance)" i on i."invoice id"=ii."invoice id"
left join "items (zoho finance)" item on item."item id"=ii."item id"
where
i."invoice date">='2021-07-01' and i."invoice date"<='2021-07-31'
group by i."sales person id"
) as t1
inner join "sales persons (zoho finance)" sp on t1."i.sales person id"=sp."sales person id";

Here's the 2nd View Table "Sales Person Commission Part 1":

select t1.*,
round(t1."Commission Percentage" * t1."Gross Sales" / 100, 2) as "Commission Total"
from
(
select *,
case 
when "Gross Profit Percentage" <= 5 then 0
when "Gross Profit Percentage" > 5 and "Gross Profit Percentage" <= 10 then 1.9
when "Gross Profit Percentage" > 10 and "Gross Profit Percentage" <= 15 then 2.9
when "Gross Profit Percentage" > 15 and "Gross Profit Percentage" <= 20 then 3.9
when "Gross Profit Percentage" > 20 and "Gross Profit Percentage" <= 25 then 4.9
when "Gross Profit Percentage" > 25 and "Gross Profit Percentage" <=30 then 5.9
when "Gross Profit Percentage" > 30 then 7.9
end as "Commission Percentage"
from "Sales Person Commission Part 1"
) as t1;

Is there a possibility to reconstruct both parts into single SQL with maximum only 1 sub-query?

===============================

Update

Problem solved! Thanks to @shadow for providing the tips. here is the final reconstructed SQL:

select t1.*,
round(t1."Commission Percentage" * t1."Gross Sales" / 100, 2) as "Commission Total"
from
(
select
sp."name" as "Sales Person",
round(sum(ii."total (bcy)"), 2) as "Gross Sales",
round(if (item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) as "Gross Partner Profit",
round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) as "Gross Profit",
round(100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) , 2) as "Gross Profit Percentage",
case
when item."partner price" is null then 7.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <= 5 then 0
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 5 and 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <= 10 then 1.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 10 and 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <= 15 then 2.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 15 and 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <= 20 then 3.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 20 and 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <= 25 then 4.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 25 and 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) <=30 then 5.9
when 100 * round(sum(ii."total (bcy)") - if(item."partner price" is null, 0, sum(item."partner price" * ii."quantity")), 2) / round(sum(ii."total (bcy)"), 2) > 30 then 7.9
end as "Commission Percentage"
from
"invoice items (zoho finance)" ii 
inner join "invoices (zoho finance)" i on i."invoice id"=ii."invoice id"
inner join "sales persons (zoho finance)" sp on sp."sales person id" = i."sales person id"
left join "items (zoho finance)" item on item."item id"=ii."item id"
where
i."invoice date">='2021-07-01' and i."invoice date"<='2021-07-31'
group by i."sales person id",sp."name", item."partner price"
order by sp."name"
) as t1;

=========================

Update 2

Replacing if field is null with coalesce(field,value)

select t1.*,
round(t1."Commission Percentage" * t1."Gross Sales" / 100, 2) as "Commission Total"
from
(
select
sp."name" as "Sales Person",
round(sum(ii."total (bcy)"), 2) as "Gross Sales",
round(coalesce(sum(item."partner price" * ii."quantity"), 0)) as "Gross Partner Profit",
round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2) as "Gross Profit",
round(100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) , 2) as "Gross Profit Percentage",
case
when item."partner price" is null then 7.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <= 5 then 0
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 5 and 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <= 10 then 1.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 10 and 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <= 15 then 2.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 15 and 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <= 20 then 3.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 20 and 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <= 25 then 4.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 25 and 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) <=30 then 5.9
when 100 * (round(sum(ii."total (bcy)"), 2) - round(coalesce(sum(item."partner price" * ii."quantity"), 0), 2)) / round(sum(ii."total (bcy)"), 2) > 30 then 7.9
end as "Commission Percentage"
from
"invoice items (zoho finance)" ii 
inner join "invoices (zoho finance)" i on i."invoice id"=ii."invoice id"
inner join "sales persons (zoho finance)" sp on sp."sales person id" = i."sales person id"
left join "items (zoho finance)" item on item."item id"=ii."item id"
where
i."invoice date">='2021-07-01' and i."invoice date"<='2021-07-31'
group by i."sales person id",sp."name", item."partner price"
) as t1 order by t1."Sales Person"

First decide please which database product the syntax should target as mysql and postgresql has different syntaxes. If this analytics tool has its own syntax, then the underlying database product is irrelevant. — Shadow, Jul 16 '21 at 08:43
@Shadow I'm using syntax from MySQL. I have updated the question to emphasize this. — mjb, Jul 16 '21 at 08:47
You have to repeat the calculation for "Gross Profit Percentage" in your case statement and calculation for and "Commission Percentage" in the outer query, then there is no need for another subquery. However, it may be better if you have two views as the resulting code would be barley legible and any changes in the logic would have to be replicated at several different places within the code making code maintenance a nightmare. — Shadow, Jul 16 '21 at 08:59
@Shadow I completed the task. I have updated/added my solution in the question. Thanks for ur tips :) — mjb, Jul 16 '21 at 09:54
Just in case you are interested, you can shorten "if field is null" expressions with coalesce(). — Shadow, Jul 16 '21 at 10:04
@Shadow Cool :) I have done another update replace if field is null with coalesce(). Thanks a lot buddy! I have added my solution into the question above. — mjb, Jul 16 '21 at 10:26
Consider adding your solution as an answer - you can accept your own answer later. — Shadow, Jul 16 '21 at 11:41

Combine 4 SQL sub-query into Single query (ZOHO Analytics)

0 Answers0