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)
THEN '$' || SUM(Sale.salePrice)
WHEN LENGTH(SUM(Sale.salePrice)
THEN '$' || SUBSTR(SUM(Sale.salePrice)
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!
Dan K
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)
THEN '$' || SUM(Sale.salePrice)
WHEN LENGTH(SUM(Sale.salePrice)
THEN '$' || SUBSTR(SUM(Sale.salePrice)
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!
Dan K
lcohan
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,
ASKER
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)
THEN '$' || SUM(Sale.salePrice)
WHEN LENGTH(SUM(Sale.salePrice)
THEN '$' || SUBSTR(SUM(Sale.salePrice)
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. ???
"Confused in Savannah"
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)
THEN '$' || SUM(Sale.salePrice)
WHEN LENGTH(SUM(Sale.salePrice)
THEN '$' || SUBSTR(SUM(Sale.salePrice)
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. ???
"Confused in Savannah"
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Best,
Dan K
...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.
Best,
Dan K
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
THEN '$' || SUM(Sale.salePrice)
WHEN LENGTH(SUM(Sale.salePrice)
THEN '$' || SUBSTR(SUM(Sale.salePrice)
END AS ''
FROM Sale
GROUP BY datetime('%Y', Sale.date)
UNION ALL
SELECT
'TOTAL TO DATE' AS '',
CASE
WHEN LENGTH(SUM(Sale.salePrice)
THEN '$' || SUM(Sale.salePrice)
WHEN LENGTH(SUM(Sale.salePrice)
THEN '$' || SUBSTR(SUM(Sale.salePrice)
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
SELECT
strftime('%Y',Sale.date) AS '',
CASE
WHEN LENGTH(SUM(Sale.salePrice)
THEN '$' || SUM(Sale.salePrice)
WHEN LENGTH(SUM(Sale.salePrice)
THEN '$' || SUBSTR(SUM(Sale.salePrice)
END AS ''
FROM Sale
GROUP BY datetime('%Y', Sale.date)
UNION ALL
SELECT
'TOTAL TO DATE' AS '',
CASE
WHEN LENGTH(SUM(Sale.salePrice)
THEN '$' || SUM(Sale.salePrice)
WHEN LENGTH(SUM(Sale.salePrice)
THEN '$' || SUBSTR(SUM(Sale.salePrice)
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
ASKER
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.
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.
ASKER
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.