Solved

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

Posted on 2016-11-07
10
36 Views
Last Modified: 2016-11-14
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!


Dan K
0
Comment
Question by:Dan Kaufman
  • 5
  • 5
10 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 41877879
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
 

Author Comment

by:Dan Kaufman
ID: 41878061
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.  ???

"Confused in Savannah"
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41878969
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
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 500 total points
ID: 41879026
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
 

Author Comment

by:Dan Kaufman
ID: 41879186
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Expert Comment

by:lcohan
ID: 41879288
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
 

Accepted Solution

by:
Dan Kaufman earned 0 total points
ID: 41880753
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
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41880785
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
 

Author Comment

by:Dan Kaufman
ID: 41880839
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
 

Author Closing Comment

by:Dan Kaufman
ID: 41886058
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now