
<h2 class="wp-block-heading"><strong>The all-important revenue graph</strong></h2>



<p>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.</p>



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



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/Revenue-currency-blog.png" alt="Revenue chart" class="wp-image-74221"/></figure>



<p>Wouldn’t you rather look at this?</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/Revenue-2-currency-blog.png" alt="A better revenue chart" class="wp-image-74236"/></figure>



<p>That’s a revenue graph we can all get behind!</p>



<p><strong>SQL Analytics Starter Kit: Best Practices, Tips, and Tricks:</strong></p>



<a class="action-btn " href="https://www.sisense.com/whitepapers/sql-analytics-best-practices-tips-and-tricks/" target="_blank" rel="noopener noreferrer">Get the Starter Kit</a>



<h2 class="wp-block-heading"><strong>Formatting the query</strong></h2>



<p>Our query starts like this:</p>



<pre class="wp-block-code"><code>select date(created_at), sum(price)
from orders
group by 1</code></pre>



<p>Let’s rewrite it to get some nice currency formatting.</p>



<h2 class="wp-block-heading"><strong>Postgres</strong></h2>



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



<pre class="wp-block-code"><code>select date(created_at), cast(sum(price) as money)
from orders
group by 1</code></pre>



<h2 class="wp-block-heading"><strong>MySQL</strong></h2>



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



<pre class="wp-block-code"><code>select date(created_at), concat('$', format(sum(price), 2))
from orders
group by 1</code></pre>



<h2 class="wp-block-heading"><strong>Redshift</strong></h2>



<p>Unfortunately Redshift doesn’t support the money type or the format function. We’ll use the <a href="https://docs.aws.amazon.com/redshift/latest/dg/r_TO_CHAR.html" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">to_char</a> function to specify an exact <a href="https://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_formating.html" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">format string</a>:</p>



<pre class="wp-block-code"><code>select 
  date(created_at), 
  to_char(sum(price), 'FM$999,999,999,990D00')
from orders
group by 1</code></pre>



<p>Our format string covers a few bases:</p>



<ul><li>FM removes leading and trailing whitespace; this allows our “$” to be right next to the number</li><li>$ is our dollar sign</li><li>The 9s are optional digits</li><li>The commas separate the thousands, millions, etc.</li><li>The 0s are required digits; all numbers will have a ones place and two decimal places, with zeroes in those places if necessary</li><li>The D specifies our decimal “.”</li><li>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</li></ul>



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



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/Dollar-button-currency-blog.png" alt="Dollar button image" class="wp-image-74231"/></figure>



<p>Or if you prefer typing to clicking, you can annotate the SQL itself!</p>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/Sum-price-currency-blog.png" alt="Revenue formatter sum price" class="wp-image-74226"/></figure>



<p><strong>SQL Analytics Starter Kit: Best Practices, Tips, and Tricks:</strong></p>



<a class="action-btn " href="https://www.sisense.com/whitepapers/sql-analytics-best-practices-tips-and-tricks/" target="_blank" rel="noopener noreferrer">Get the Starter Kit</a>


How to Format Numbers as Currency in Postgres, MySQL and Redshift

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article