inner join results of "with" clause tables

January 16, 2022

I’ve 4 ‘with’ clause tables as follows

with total_sales as (select sum("REVENUE") as summ from sql_sales) ,
     total_items as (select count("ID_ORDER") as oo from sql_sales) ,
     order_per_country as (select c."COUNTRY" as country, count(s."ID_ORDER") as orders
                        from sql_sales s
                        join sql_country c on s."ID_SELLER_COUNTRY" = c."ID_COUNTRY"
                        group by c."COUNTRY") , 
    revenue_per_country as (select c."COUNTRY" as country, sum(s."REVENUE") as REVENUE
                        from sql_sales s
                        join sql_country c on s."ID_SELLER_COUNTRY" = c."ID_COUNTRY"
                        group by c."COUNTRY")

now i’d like to display final table with 2 columns (each country) and (revenue per country / total sales) , but I don’t know how

>Solution :

You are complicating quite a bit the query, you can resolve it with basic sql aggregation:

select 
   c.COUNTRY as country, 
   sum(s.REVENUE)/(select sum(REVENUE) as total_revenue from sql_sales) as Percentage
from sql_country c
  join sql_sales ss
    on s.ID_SELLER_COUNTRY = c.ID_COUNTRY
group by c.COUNTRY