SQlite How to format SUM() CASE() expression values?
Posted on 2016-11-07
Good morning Experts,
I have a VIEW in my SQLite "Edition Catalog" that shows Print Sale Totals by Year:
'Print Sales' AS "",
WHEN Sale.date BETWEEN '2012-01-01' AND '2012-12-31'
END) AS '2012',
WHEN Sale.date BETWEEN '2014-01-01' AND '2014-12-31'
END) AS '2014',
/* which is repeated for previous and successive years with the WHEN / BETWEEN dates edited appropriately */
and then a Total Sales To Date column.
WHEN LENGTH(SUM(Sale.salePrice)) = 3
THEN '$' || SUM(Sale.salePrice)
WHEN LENGTH(SUM(Sale.salePrice)) > 3
THEN '$' || SUBSTR(SUM(Sale.salePrice),-6,3) || ',' || SUBSTR(SUM(Sale.salePrice),-3,3)
END AS 'TOTAL SALES TO DATE'
"All of the above" is no problem, works fine for me. My problem and question relates to the STRING FORMATTING I have added to the TOTAL SALES TO DATE column. What you see here works without issue.
What I am unable to do is apply the same or similar STRING FORMATTING to any of the SUM(CASE... expressions for the individual years. I have shown the expression for 2014 as an example.
Here is a sample Result Set I get from the full VIEW query:
2012 2013 2014 2015 2016 TOTAL SALES TO DATE
"Print Sales" "700" "9377" "9850" "6275" "$26,202"
As you see I have simply applied a CONCATENTATEd string format to display the Sales Totals with a $ sign and commas. This works fine on the "Totals To Date" column ONLY.
However, in the Year columns, such as 2012 and 2014 above, the expression value:
THEN Sale.salePrice --this expression works, it gives the correct result value--
When I try apply any formatting to the value ( Sale.salePrice ), such as:
THEN '$' || Sale.salePrice --this expression "voids the result" and gives me 0.0 as the result value--
I get a result value of 0.0 versus say <NULL> for 2012, 700 for 2013, or 9377 for 2014
So...my question then to Experts one and all is:
How can I apply my "string formatting" to the THEN Sale.salePrice values in my SUM(CASE... expressions?
As you see in my successful formatting of the TOTAL SALES TO DATE column I have used a CASE WHEN expression to test for values that are only hundreds of dollars in values, to add just a $ sign, and then values in the thousands to add a $ sign and comma. (I'd love to sell a print for $1,000,000 but I don't think that's realistic so I stopped my WHEN testing at "just the thousands...") ...and then I run the SUM(Sale.salePrice) calculation in the Result Value line. Could this be what I should be doing in the individual year SELECTs?
Thanks in advance so much for your experience that counts!