~3 min read|
In my post on how to use CTEs to write legible queries, I skipped over a potentially useful thing in SQL: how to add a column of calculated data to a query result.
What do I even mean by that? In the example I was using, I was looking at the sales data for Dunder Mifflin. Rows and rows of sales data.
Now, imagine you want to see the size of a particular order to all orders in a time period.
One way to do this is with a cross join. Cross joins have the potential for fanning out - creating many new rows. In our case though, we’ll avoid that because we’ll be cross joining with a view that has exactly one row.
Gets summarized to:
So, then the cross join will be:
How do you do this with SQL though?
SELECT d.order_id, d.account_id, d.quantity, SUM(d.quantity) OVER() FROM tutorial.dunder_mifflin_paper_sales AS d GROUP BY 1, 2, 3 ORDER BY 4 DESC
This is easy enough in this contrived example. As things get more complex, we might consider using a CTE. This keeps the logic separate and bite-sized.
WITH total_qty AS ( SELECT sum (d.quantity) AS qty FROM tutorial.dunder_mifflin_paper_sales AS d ) SELECT d.order_id, d.account_id, d.quantity, t.qty FROM tutorial.dunder_mifflin_paper_sales AS d CROSS JOIN total_qty AS t ORDER BY 4 DESC
Dealer’s choice here, but I definitely lean toward the CTE as the queries get more complicated.
Hi there and thanks for reading! My name's Stephen. I live in Chicago with my wife, Kate, and dog, Finn. Want more? See about and get in touch!