Use Alias from one select to be used in another select

My table looks something like this

TransactionID     Establishment    Client

TR001             Estab1           XXX
TR002             Estab1           YYY
...
TR00X             EstabN           XXX

I need to count how many sales to specific clients (XX, YY and ZZ) each establishments made, and the total % this number represents comparing to all the sales done by this establishement. The result should look something like this:

Establishment    Client    Nbr_Sales    %Total_sales

Estab1           XXX       64           0.35%
Estab1           YYY       321          0.17%
Estab2           XXX       2325         25%
...

I wrote a request to get the total number of sales for each establishment:

SELECT Establishment, Count(*) AS tot_sales_by_estab
FROM data_list 
GROUP BY Establishment;

This request gives me the total number of sales each establishement has done to the "XX" "YY" and "ZZ" clients

SELECT Establishment, Client, Count(*) as Nbr_Sales
FROM data_list 
WHERE Client = 'XX' OR Client = 'YY' OR Client = 'ZZ'
GROUP BY Establishment, Client;

I just need to add another column to this request using the previous alias "tot_sales_by_estab" and do something like

ROUND((Nbr_Sales / tot_sales_by_estab)*100, 4) AS %Total_sales

But I cannot figure out how I am supposed to do it

Solution

It would be easier if you had provided sample input and output so we could have tested.

To connect two result set we usually use JOIN using a common column between the result sets.

The common column in your result sets is Establishment, so we could INNER JOIN using establishment.

total_sales and client_sales are the CTE which essentially contains the code you provided.

WITH total_sales AS (
    SELECT 
        Establishment, 
        COUNT(*) AS tot_sales_by_estab
    FROM 
        data_list 
    GROUP BY 
        Establishment
),

client_sales AS (
    SELECT 
        Establishment, 
        client, 
        COUNT(*) AS Nbr_Sales
    FROM 
        data_list 
    WHERE 
        client IN ('XXX', 'YYY', 'ZZZ')
    GROUP BY 
        Establishment, client
)

SELECT 
    cs.Establishment,
    cs.Client,
    cs.Nbr_Sales,
    ROUND((cs.Nbr_Sales * 100.0 / ts.tot_sales_by_estab), 2) AS %Total_sales
FROM 
    client_sales cs
INNER JOIN 
    total_sales ts ON cs.Establishment = ts.Establishment
ORDER BY 
    cs.Establishment, cs.Client;

EDIT : without CTE

SELECT 
    dl.Establishment,
    dl.Client,
    COUNT(*) AS Nbr_Sales,
    ROUND((COUNT(*) * 100.0 / total_sales.tot_sales_by_estab), 2) AS %Total_sales
FROM 
    data_list dl
JOIN 
    (SELECT Establishment, COUNT(*) AS tot_sales_by_estab
     FROM data_list
     GROUP BY Establishment) AS total_sales
ON 
    dl.Establishment = total_sales.Establishment
WHERE 
    dl.Client IN ('XXX', 'YYY', 'ZZZ')
GROUP BY 
    dl.Establishment, dl.Client
ORDER BY 
    dl.Establishment, dl.Client;