sql: how to append a generated value to each row

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?

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

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

• ¹ More discussion on OVER() clauses on Stack Overflow