The all-important revenue graph

In your venerable orders table, you’re almost certainly storing prices as numbers. Perhaps they’re integer, perhaps they’re numeric, perhaps you’re using Postgres and they’re money, or perhaps you rolled the dice on floating-point rounding errors and went with real.

But save for Postgres’s money format, your revenue graph looks, well, not like revenue at all:

Revenue chart

Wouldn’t you rather look at this?

A better revenue chart

That’s a revenue graph we can all get behind!

SQL Analytics Starter Kit: Best Practices, Tips, and Tricks:

Get the Starter Kit

Formatting the query

Our query starts like this:

select date(created_at), sum(price)
from orders
group by 1

Let’s rewrite it to get some nice currency formatting.


Assuming you’re not already using the money type, you can leverage it for some quick formatting:

select date(created_at), cast(sum(price) as money)
from orders
group by 1


Things in MySQL aren’t quite so easy. But we can still format the number to get two decimal places and prepend a “$”:

select date(created_at), concat('$', format(sum(price), 2))
from orders
group by 1


Unfortunately Redshift doesn’t support the money type or the format function. We’ll use the to_char function to specify an exact format string:

  to_char(sum(price), 'FM$999,999,999,990D00')
from orders
group by 1

Our format string covers a few bases:

  • FM removes leading and trailing whitespace; this allows our “$” to be right next to the number
  • $ is our dollar sign
  • The 9s are optional digits
  • The commas separate the thousands, millions, etc.
  • The 0s are required digits; all numbers will have a ones place and two decimal places, with zeroes in those places if necessary
  • The D specifies our decimal “.”
  • This SQL works on all 3 of these databases, but it’s a bit onerous to type, so we prefer the other options on Postgres and MySQL

Of course, using a tool like Sisense for Cloud Data Teams, you can reduce all of this to a single click:

Dollar button image

Or if you prefer typing to clicking, you can annotate the SQL itself!

Revenue formatter sum price

SQL Analytics Starter Kit: Best Practices, Tips, and Tricks:

Get the Starter Kit