• Welcome, Guest. Please login.
 
October 21, 2019, 02:32:23 am

News:

Welcome to the SQLitening support forums!


Group by Questions

Started by D. Wilson, February 11, 2018, 07:43:11 pm

Previous topic - Next topic

D. Wilson

I am stuck on another SQL Query. It is something that I know should be able to be done. I just can't figure out how to do it. I want to a group by and sum of a field. The 'grouping' field is numeric and 5 digits.

FYI -- The grouping field is a Sales Category. The first 2 digits represent the major catagory; and the remaining digits allow us to refine our inventory by sub categories.

Raw data:

                 51000              $10.00
                 51100              $5.00
                 62500              $5.00
                 70000              $10.00
                 70500              $10.00


'Desired Results'

                 51000       51             $15.00
                 62000       62             $5.00
                 70000       70             $20.00

Basically I am subtotaling by the first two digits of the numeric value.

I am trying to create an SQL Query to get the report data. Any ideas or suggestions would be greatly appreciated.

Thank you for your assistance.

cj

February 12, 2018, 12:36:28 am #1 Last Edit: February 12, 2018, 02:09:24 am by cj
I use INTEGER for currency and multiply by .01 
I do not know if this is better with SQLite to avoid rounding errors?  printf('%.2f',sum(amount)*.01)

#INCLUDE "sqlitening.inc"
%KillTable=0
FUNCTION PBMAIN () AS LONG
LOCAL sql      AS STRING
LOCAL sArray() AS STRING
slOpen "test.db3","C"
IF %KillTable THEN slexe "drop table if exists MyTable"
slexe "create table if not exists MyTable(code integer,amount integer)"
slexe "insert into MyTable values(51000,1000)"
slexe "insert into MyTable values(51100,500)"
slexe "insert into MyTable values(62500,500)"
slexe "insert into MyTable values(70000,1000)"
slexe "insert into MyTable values(70500,1000)"
sql="select code/1000 * 1000 as Cat,code/1000 as Major, printf('%.2f',sum(amount)*.01) as Total from MyTable group by code/1000"
slSelAry sql,sArray(),"Q9"
? JOIN$(sArray(),$CR),,"Sales by Category"
END FUNCTION

D. Wilson

CJ Thanks for the help. Over the next couple of days I will try and get it tested and i will post my results.

The key that I was missing was the ability to divide by 1000 then do the grouping.

Once againg - Thank you.

cj


Bern Ertl

If the sales category is stored as an integer/numeric, you can use the following SQL to safely extract the first two digits:

SELECT CAST( substr( CAST( SalesCategory, TEXT),1, 2), INTEGER) AS CatAbbr,  ...

cj

February 12, 2018, 12:08:29 pm #5 Last Edit: February 12, 2018, 12:20:18 pm by cj
Bern,

cj code works using /1000:
sql="SELECT SalesCategory/1000 *1000 AS CAT,SalesCategory/1000 as MAJOR, printf('%.2f',sum(amount)*.01) as TOTAL from MyTable group by SalesCategory/1000"

Bern code works using SUBSTR without CAST:
sql="SELECT SalesCategory/1000 *1000 AS CAT,SUBSTR(SalesCategory,1,2) as MAJOR, printf('%.2f',sum(amount)*.01) as TOTAL from MyTable group by substr(SalesCategory,1,2)"

Syntax error with CAST:
sql="SELECT CAST( substr( CAST( SalesCategory, TEXT),1, 2), INTEGER) AS Cat from MyTable"
#INCLUDE "sqlitening.inc"
%KillTable=1
FUNCTION PBMAIN () AS LONG
LOCAL sql      AS STRING
LOCAL sArray() AS STRING
slOpen "test.db3","C"
IF %KillTable THEN slexe "drop table if exists MyTable"
slexe "create table if not exists MyTable(SalesCategory integer,amount integer)"
slexe "insert into MyTable values(51000,1000)"
slexe "insert into MyTable values(51100,500)"
slexe "insert into MyTable values(62500,500)"
slexe "insert into MyTable values(70000,1000)"
slexe "insert into MyTable values(70500,1000)"

sql="SELECT SalesCategory/1000 *1000 AS CAT,SalesCategory/1000 as MAJOR, printf('%.2f',sum(amount)*.01) as TOTAL from MyTable group by SalesCategory/1000"
sql="SELECT SalesCategory/1000 *1000 AS CAT,SUBSTR(SalesCategory,1,2) as MAJOR, printf('%.2f',sum(amount)*.01) as TOTAL from MyTable group by substr(SalesCategory,1,2)"
sql="SELECT CAST( substr( CAST( SalesCategory, TEXT),1, 2), INTEGER) AS Cat from MyTable"

slSelAry sql,sArray(),"Q9"
? JOIN$(sArray(),$CR),,"Sales by Category"
END FUNCTION

                                                                 

Bern Ertl

February 12, 2018, 12:46:26 pm #6 Last Edit: February 12, 2018, 12:56:18 pm by Bern Ertl
Damn.  My mistake... correct syntax ( http://www.sqlite.org/lang_expr.html ) is:  CAST ( expr AS type)

Try:

sql="SELECT CAST( substr( CAST( SalesCategory AS TEXT),1, 2) AS INTEGER) AS Cat from MyTable"

edit:  cj, you might try testing your /1000 code with data elements like 51999 or so to ensure there are no rounding errors.  I'm not sure how SQLite handles rounding and/or conversion of real numbers in intermediate calculations to integer final results.  The text extraction I proposed should always work regardless of SQLite's data type handling and/or potential rounding with (real number?) division of integers.

cj

Haven't seen any rounding errors.
DW is going to get back in a couple days with his findings.

#INCLUDE "sqlitening.inc"
%KillTable=1
FUNCTION PBMAIN () AS LONG
LOCAL sql      AS STRING
LOCAL sArray() AS STRING
slOpen "test.db3","C"
IF %KillTable THEN slexe "drop table if exists MyTable"
slexe "create table if not exists MyTable(SalesCategory integer,amount integer)"

slexe "insert into MyTable values(10000,100)"   '2
slexe "insert into MyTable values(10001,100)"

slexe "insert into MyTable values(51000,100)"   '1

slexe "insert into MyTable values(52999,100)"   '4
slexe "insert into MyTable values(52232,100)"
slexe "insert into MyTable values(52753,100)"
slexe "insert into MyTable values(52000,100)"

slexe "insert into MyTable values(64050,100)"   '1

slexe "insert into MyTable values(65500,100)"   '2
slexe "insert into MyTable values(65499,100)"

slexe "insert into MyTable values(99999,100)"   '1

sql="SELECT SalesCategory/1000 *1000 AS CAT,SalesCategory/1000 as MAJOR, printf('%.2f',sum(amount)*.01) as TOTAL from MyTable group by SalesCategory/1000"
'sql="SELECT SalesCategory/1000 *1000 AS CAT,SUBSTR(SalesCategory,1,2) as MAJOR, printf('%.2f',sum(amount)*.01) as TOTAL from MyTable group by substr(SalesCategory,1,2)"
'sql="SELECT CAST( substr( CAST( SalesCategory AS TEXT),1, 2) AS INTEGER) AS Cat from MyTable"
slSelAry sql,sArray(),"Q9"
? JOIN$(sArray(),$CR),,"Sales by Category"
END FUNCTION