subquery (nested query) can use columns from the tables listed in the main query

In short, a nested query works IndependentOf the enclosing SQL statement and can make use of any of the column values From the tables listed in the enclosing statement ' s FROM clause. You can use the nested queries to perform multi-table operations without have to JOIN rows in multiple related tables. However, if you need the data values from multiple tables, or if you want individual column values and aggregate function Valu Es in the same row in the results table, you can nest a subquery with the aggregate function so you need in the SELECT C Lause of a multi-table query or JOIN.

Select
    trade_date,
    symbol,
    shares * Price as "total trade",
    (
        select
            COUNT (*)
            from Trades
        WHERE
            trade_date > GETDATE ()-365 and
        cust_id = CID--cid is in Customer not the trades which is T He is table in this
            --subquery's FROM clause
    ) as ' Count ',
    (SELECT sum (price
            ) * SUM (shares) C16/>from
            Trades
        WHERE
            trades.trade_date >= GETDATE ()-365 and
        cust_id = CID
    ) as                       ' Total $ Volume) ',
    CID as                     ' Cust ID ',
    TRIM (f_name) + ' +l_name as ' Customer ' from
    EI. CUSTOMERS
JOIN EI. TRADES
on
    CID = cust_cid
WHERE
    shares * Price >= 100000 and
trade_date >= GETDATE () -365< C34/>order by
    customer;