sql: how to append a generated value to each row

2022-07-07

 | 

~3 min read

 | 

409 words

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.

order_id quantity account_id
1 10 a
2 5 a
3 15 b
4 2 c
5 8 c
6 20 c

Gets summarized to:

total_qty
60

So, then the cross join will be:

order_id quantity account_id total_qty
1 10 a 60
2 5 a 60
3 15 b 60
4 2 c 60
5 8 c 60
6 20 c 60

How do you do this with SQL though?

One easy way is to use the SUM function and then use an empty OVER() clause to disregard the GROUP BY:1

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.

Footnotes


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!