0

I have a table that looks like this:

The columns are labeled ptime, quantity, and sales amount.

"2015-10-15 9:50:09"    "2" "6"
"2015-10-4 10:24:48"    "3" "9"
"2015-10-8 12:45:46"    "5" "15"
"2015-10-8 9:50:09"     "3" "9"
"2015-10-8 10:24:48"    "1" "3"
"2015-10-9 12:45:46"    "2" "6"
"2015-10-20 12:45:46"   "2" "8"
"2015-10-2 23:45:52"    "2" "6"
"2015-10-8 15:21:23"    "3" "9"
"2015-10-12 21:31:47"   "2" "6"
"2015-9-6 12:50:09"     "1" "3"
"2015-9-15 16:38:21"    "1" "3"
"2015-9-2 23:45:52"     "1" "3"
"2015-9-8 15:21:23"     "1" "3"

I am using the following code to sum sales amount by month and year:

sum(SalesAmount) as SalesMonth,
strftime("%m-%Y", ptime) as 'month-year' 
from Sales
group by strftime("%m-%Y", ptime);

However, the results I am getting show 

"81"    "NULL"
"14"    "10-2015"

Which is not the correct sum for the months of September and October.

Any help is much appreciated.

Thanks.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
user5441357
  • 9
  • You gave some sample data, a query and some sample results. Are the sample results the *entire* result set that is given by executing the given query against the given dataset? Because it appears that your results are an excerpt from a result set that was run against some larger data set. That query, executed against the provided dataset should not produce those results. – DeadZone Oct 26 '15 at 17:41
  • The table I provided is a sample, but the query results are from the entire dataset. Regardless though, the query results are not summing the values correctly from the entire dataset as you can see -- it lists only 14 for October when you can see more than that in the sales amount column for even the sample I posted. Also, it is not identifying September. – user5441357 Oct 26 '15 at 17:45
  • Are all three columns character columns? From the quotation marks, it would appear to be the case. If so, are you certain that all of your dates are formatted correctly? I'm not an expert in SQLLite, but I have played with it a little and have seen the `strftime()` function return NULL when things are not exactly perfect. My guess is much of October and all of September are getting formatted to NULL by `strftime()`. – DeadZone Oct 26 '15 at 18:03
  • Thanks! I do not think they are character - the schema says that they are num(ptime) integer(quantity). Do you have a suggestion on how to modify my code? – user5441357 Oct 26 '15 at 18:23
  • Perhaps you can create a user defined function that parses the month from your date format. It's not trivial, but it should suffice. http://stackoverflow.com/questions/7867099 – tidwall Oct 26 '15 at 19:52

1 Answers1

0

strftime() does not work because you are not using one of the supported date formats; all fields must be padded to two digits.

CL.
  • 173,858
  • 17
  • 217
  • 259