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:
Wouldn’t you rather look at this?
That’s a revenue graph we can all get behind!
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
select date(created_at), 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:
Or if you prefer typing to clicking, you can annotate the SQL itself!