SQlite How to format SUM() CASE() expression values?

Good morning Experts,

I have a VIEW in my SQLite "Edition Catalog" that shows Print Sale Totals by Year:

SELECT
'Print Sales' AS "",
SUM(CASE
WHEN Sale.date BETWEEN '2012-01-01' AND '2012-12-31'
THEN Sale.salePrice
END) AS '2012',
...

SUM(CASE
WHEN Sale.date BETWEEN '2014-01-01' AND '2014-12-31'
THEN Sale.salePrice
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.

CASE
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'
FROM
Sale
;

"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!

what is it for 2012? is it NULL maybe so the functions return void on some NULL value? maybe you need to add ISNULL check on the SUM before concatenation? or maybe on each term of the sum would be better? like to say ... SUM(ISNULL(Sale.salePrice, 0, Sale.salePrice))

0

Dan KaufmanFine Art PhotographerAuthor Commented:

Thank you Icohan, I have only just started entering data to the tables, thus 2012 is NULL only because no sales/data for that year are present.

The problem isn't in the SUM value, NULL or not, for Sale.salePrice for the date range of 2012, but in the "how to" format the value.

Here's the query statement for the current year and the Total Sales to Date:

SUM(CASE
WHEN Sale.date BETWEEN '2016-01-01' AND '2016-12-31'
THEN Sale.salePrice
END) AS '2016',
CASE
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',

For the final TOTAL SALES... statement I have formatted the SUM(Sale.salePrice) value to include a $ sign and comma (if the value is equal or greater than 1,000.

BUT...when I try to apply the same, or similar, formatting to ANY of the individual date ranges...2013, 2014, ... the result value is rendered as 0.0. This is what is so confusing to me.

In any/all of the individual year statements I use a simple DATE RANGE such as:
WHEN Sale.date BETWEEN '2013-01-01' AND '2013-12-31 to get the salePrice values for that year and display it with the THEN line:
THEN Sale.salePrice

IF I put any formatting at all on that, such as:
THEN '$' || Sale.salePrice

then the result value for say 2013 which is 700, or 2014 which is 9377, immediately shows as 0.0

I have no clue as to why this is happening and thus my dilemma as to how to fix it so the VIEW displays as $700 for 2013, $9,377 for 2014, etc.

I can do it for the TOTAL SALES TO DATE statement but not for any of the individual year statements. ???

quick fix I'm thinking is to put a view on a top of another view and add formatting only in the warper so there's no function like SUM or anything else other than formatting the SUMmed values - make sense? I hope I'll have some time soon to do a test on my own...

0

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I know is "clunky" solution however I believe code like below should work until a better one to have a single view can be found

create view sum_view as
select
SUM(CASE
WHEN Sale.date BETWEEN '2012-01-01' AND '2012-12-31'
THEN Sale.salePrice
END) AS 'Sales_2012',
SUM(CASE
WHEN Sale.date BETWEEN '2013-01-01' AND '2013-12-31'
THEN Sale.salePrice
END) AS 'Sales_2013',
SUM(CASE
WHEN Sale.date BETWEEN '2014-01-01' AND '2014-12-31'
THEN Sale.salePrice
END) AS 'Sales_2014',
SUM(CASE
WHEN Sale.date BETWEEN '2015-01-01' AND '2015-12-31'
THEN Sale.salePrice
END) AS 'Sales_2015',
SUM(CASE
WHEN Sale.date BETWEEN '2016-01-01' AND '2016-12-31'
THEN Sale.salePrice
END) AS 'Sales_2016',
SUM(Sale.salePrice) AS 'TOTAL_SALES'

create view wraper_view as
select
case WHEN LENGTH(Sales_2012) = 3
THEN '$' || Sales_2012
WHEN LENGTH(Sales_2012) > 3
THEN '$' || SUBSTR(Sales_2014,-6,3) || ',' || SUBSTR(Sales_2012),-3,3)
END AS 'Sales_2012',
case WHEN LENGTH(Sales_2013) = 3
THEN '$' || Sales_2013
WHEN LENGTH(Sales_2013) > 3
THEN '$' || SUBSTR(Sales_2013,-6,3) || ',' || SUBSTR(Sales_2013),-3,3)
END AS 'Sales_2013',
case WHEN LENGTH(Sales_2014) = 3
THEN '$' || Sales_2014
WHEN LENGTH(Sales_2014) > 3
THEN '$' || SUBSTR(Sales_2014,-6,3) || ',' || SUBSTR(Sales_2014,-3,3)
END AS 'Sales_2014',
case WHEN LENGTH(Sales_2015) = 3
THEN '$' || Sales_2015
WHEN LENGTH(Sales_2015) > 3
THEN '$' || SUBSTR(Sales_2015,-6,3) || ',' || SUBSTR(Sales_2015,-3,3)
END AS 'Sales_2015',
case WHEN LENGTH(Sales_2016) = 3
THEN '$' || Sales_2016
WHEN LENGTH(Sales_2016) > 3
THEN '$' || SUBSTR(Sales_2016,-6,3) || ',' || SUBSTR(Sales_2016,-3,3)
END AS 'Sales_2016',
case WHEN LENGTH(TOTAL_SALES) = 3
THEN '$' || TOTAL_SALES
WHEN LENGTH(TOTAL_SALES) > 3
THEN '$' || SUBSTR(TOTAL_SALES,-6,3) || ',' || SUBSTR(TOTAL_SALES,-3,3)
END AS 'TOTAL_SALES'

0

Dan KaufmanFine Art PhotographerAuthor Commented:

Thank you lcohan,

...correct me if I'm wrong, but I believe you're identifying the rub of the problem as "you can't apply formatting to a SUM(CASE...
expression"...SO, you've divided the problem into a "Base Calculation_table" --where all the SUM(CASE... work is done; and then using a "Results Presentation_table"--VIEW where the formatting is applied. Right?

Your "clunky" solution may be a bit "wordy" but it appears to get the job done. I haven't had time to implement a test, but "By George, I think you've got it." Thanks!

...I am STILL CURIOUS however about the why and wherefore of the root problem: what is it in the SUM(CASE... expression that is causing the result to fail when simple STRING FORMATTING is applied?

AND I will implement your "clunky solution" later today and get back to you...but I do believe you've got it AND thank you so much for helping me out.

I'll still try to make it work as single view because maintenance on these can become a hassle to add every new year...I'll try see if I can do some sort of pivot/cross-tab/group by including formatting in a single view select statement - there must be a way and I just need to find the time to do it.

0

Dan KaufmanFine Art PhotographerAuthor Commented:

Good morning Icohan,

I have concocted a Solution to the Question at hand (How to format SUM(CASE... expression values?)
using a single VIEW statement using UNION ALL operators to "join" individual queries for each Year.

It's not as elegant as as I would hope...and I do hope that either you, Icohan, or other Experts can still show me the inefficiency of my ways. In terms of future maintenance all that really needs to be done is edit in a "copy" of the last individual "year query" and change the WHERE clause date to 2017, or 2018, etc.

One "non-elegant" aspect that I'd like to improve on is my VIEW is now a "2 column, multi-row" VIEW but has no "column headers", only row-headers. It works of course, and over time will work better perhaps than a very wide VIEW with each year as a column...
BUT I'd still like to figure out how to put simple Column Headers in: Year ...and... Total

Here's the current version of the code. It works as I said, but I'm still looking to LEARN why I was unable to apply "string formatting" to the CASE...THEN result-value. (see the original question of this thread.)

Thanks again for your Expert help Icohan. You inspired me to not give up. I'd love to hear your take on my solution below *** which I will mark as an Assisted Solution with credit due to Icohan. (I couldn't have done it without you.)

/* SALES SUMMARY BY YEAR with TOTAL TO DATE */
SELECT
'2012' AS '',
CASE
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 ''
FROM Sale
WHERE Sale.date LIKE '%2012%'

UNION ALL

SELECT
'2013' AS '',
CASE
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 ''
FROM Sale
WHERE Sale.date LIKE '%2013%'

UNION ALL

SELECT
'2014' AS '',
CASE
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 ''
FROM Sale
WHERE Sale.date LIKE '%2014%'

UNION ALL

SELECT
'2015' AS '',
CASE
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 ''
FROM Sale
WHERE Sale.date LIKE '%2015%'

UNION ALL

SELECT
'2016' AS '',
CASE
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 ''
FROM Sale
WHERE Sale.date LIKE '%2016%'

UNION ALL

SELECT
'TOTAL TO DATE' AS '',
CASE
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 ''
FROM Sale
;

Currently this gives me a Result Set as: (and as I said above, I'd love to get Column Headers on this result set.)

2012
2013 $700
2014 $9,377
2015 $9,850
2016 $6,725
TOTAL TO DATE $26,652

Well you got it one step closer and I think we should be using some datetime function like SELECT strftime('%Y','now'); to not have to hard-code and maintain the view including the fact that SELECT strftime('%Y','now'); can be used to return the year from Sale.date as a string - right? so should be something like below and sorry I can't check the syntax as I'm away from my test environment at the moment but all functions are described here: https://www.sqlite.org/lang_datefunc.html

SELECT
strftime('%Y',Sale.date) AS '',
CASE
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 ''
FROM Sale
GROUP BY datetime('%Y', Sale.date)

UNION ALL

SELECT
'TOTAL TO DATE' AS '',
CASE
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 ''
FROM Sale
;

Also this should be able to be further combined to use a PIVOT on the view to get the exact layout you wanted and the pivot function is described in a example here: http://modern-sql.com/use-case/pivot for all DB's including SQLlite

0

Dan KaufmanFine Art PhotographerAuthor Commented:

Alas...this is giving a similar and strictly speaking, non-accurate result. The sales total for 2016 from the sample date is $6,725.
Here's the result set:

2016 $26,652
TOTAL TO DATE $26,652

Anxious to see what you find when you get back to your test environment.

0

Dan KaufmanFine Art PhotographerAuthor Commented:

My solution achieves the overall goal of applying string formatting to a SUM(CASE... result value but not as expected per my original question. My "rewrite" of the VIEW code using a UNION ALL operator structure enabled me to apply the string formatting I desired...thus "solving the problem" but not strictly speaking answering my original question.

0

Featured Post

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.