title: presto sql - aggregate product of two columns with numbers stored as strings
body: <p>I am trying to  aggregate product of two columns where numbers are stored as  strings.  I tried to convert the columns into numeric values then multiply and aggregate, but I am getting errors. </p>
  
  <p><br>
  <strong>The query is built in Amazon/Athena environment  (Presto)</strong> </p>
  
  <p><br>
  <strong>UPDATE</strong> <br>
  <strong>After further investigation I found that in the dataset there are some records with negative prices and those most likely are causing the problem here</strong></p>
  
  <p><br>
  There is the example:</p>
  
  <p><strong>"dataset"."table1"</strong></p>
  
  <pre><code>product   price    quantity
  ==========================
  (string) (string)  (string)
   A        5         1 
   A       -1        1
  ...
  </code></pre>
  
  <p><strong>SQL Code</strong></p>
  
  <pre><code> SELECT 
      product
      , sum ( coalesce(cast(nullif(price,'') as DECIMAL(28, 2)),0) * coalesce(cast(nullif(quantity,'') as DECIMAL(28, 2)),0))
   FROM "dataset"."table1" 
   WHERE
      price is not NULL and price not like '0' 
      and quantity is not NULL  and  quantity not like '0' 
      GROUP BY
      product
  </code></pre>
  
  <blockquote>
    <p>ERROR: INVALID_CAST_ARGUMENT: Cannot cast VARCHAR ' SUBS' to DECIMAL(28, 2)</p>
  </blockquote>
  
  <p>The below query works fine though</p>
  
  <pre><code>SELECT 
  product
  ,coalesce(cast(nullif(price,'') as DECIMAL(28, 2)),0)
  ,coalesce(cast(nullif(quantity,'') as DECIMAL(28, 2)),0)
  from "dataset"."table1" 
  WHERE
  price is not NULL and price not like '0' 
  and quantity is not NULL  and  quantity not like '0' 
  </code></pre>
  
  <p><strong>How to work around the conversions and aggregate the product of the two columns?</strong></p>