GROUP BY: Portfolio Summary by Ticker

GROUP BY - Turn 200 Trades Into One Dashboard

Scene: 8:05am on the trading desk. The head of desk drops a coffee on your keyboard: "I don't want to scroll 200 trades. Give me one line per name, split BUY versus SELL - count, shares, and notional. Two minutes." The raw trade_ledger has 200 rows. What she wants has just 10. Getting from 200 to 10 is the single most important move in analytical SQL, and it rides on one keyword: GROUP BY.

🧠 The mental model: sorting the mailroom

Picture 200 trade tickets dumped on a table. GROUP BY ticker, side is you walking over and sorting them into labelled pigeonholes - one hole for AAPL / BUY, one for AAPL / SELL, one for MSFT / BUY, and so on. Once every ticket is in its hole, an aggregate function answers a question about each hole without you reading the tickets one at a time:

• COUNT(*) - how many tickets are in this hole?
• SUM(quantity) - add up the shares across the hole.
• SUM(quantity * price) - add up the notional (dollars) across the hole.

The columns you name in GROUP BY are the labels on the pigeonholes. Everything else in your SELECT must be an aggregate - because a hole full of tickets no longer has a single price, only a sum or an average of prices.

Aggregate functions you'll use daily

The golden rule

Every column in your SELECT is either (a) listed in GROUP BY or (b) wrapped in an aggregate. There is no third option. If you SELECT a bare ticker but forget to put it in GROUP BY, the database has no single ticker to show for the whole pile - so it collapses everything into one arbitrary row (or, on a strict engine, errors). The GROUP BY line is what defines the buckets.

Full shape of an aggregation query

SELECT   ticker,
         COUNT(*)           AS trade_count,
         SUM(quantity)      AS total_shares,
         AVG(price)         AS avg_price
FROM     trade_ledger
WHERE    side = 'BUY'        -- filter rows BEFORE grouping
GROUP BY ticker              -- define the buckets
HAVING   COUNT(*) > 5        -- filter buckets AFTER grouping
ORDER BY total_shares DESC;

Your Task

The query below already SELECTs the right columns and aggregates them - but the GROUP BY line is wrong on purpose. It groups by side alone, so all five tickers get crushed into just two rows (one BUY, one SELL) and the per-name breakdown your boss asked for vanishes. Your job is to fix the one line that defines the buckets, so you get a row for each ticker AND each side.

Predict before you run: there are 5 tickers (AAPL, MSFT, JPM, GS, SPY) and 2 sides (BUY, SELL). If GROUP BY ticker, side gives one row per combination, how many rows should the corrected query return? Write your guess down, run the broken starter first (count its rows), then fix the GROUP BY and see if you were right.