Author Topic: Group by Questions  (Read 112 times)

D. Wilson

  • Guru
  • ***
  • Posts: 193
    • View Profile
Group by Questions
« on: February 11, 2018, 06:13:11 PM »
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

  • Master Geek
  • ****
  • Posts: 626
    • View Profile
Re: Group by Questions
« Reply #1 on: February 11, 2018, 11:06:28 PM »
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
« Last Edit: February 12, 2018, 12:39:24 AM by cj »

D. Wilson

  • Guru
  • ***
  • Posts: 193
    • View Profile
Re: Group by Questions
« Reply #2 on: February 12, 2018, 12:37:52 AM »
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

  • Master Geek
  • ****
  • Posts: 626
    • View Profile
Re: Group by Questions
« Reply #3 on: February 12, 2018, 12:38:50 AM »
Welcome!

Bern Ertl

  • Master Geek
  • ****
  • Posts: 475
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Group by Questions
« Reply #4 on: February 12, 2018, 10:03:25 AM »
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

  • Master Geek
  • ****
  • Posts: 626
    • View Profile
Re: Group by Questions
« Reply #5 on: February 12, 2018, 10:38:29 AM »
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"
Code: [Select]
#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
                                                                 
« Last Edit: February 12, 2018, 10:50:18 AM by cj »

Bern Ertl

  • Master Geek
  • ****
  • Posts: 475
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Group by Questions
« Reply #6 on: February 12, 2018, 11:16:26 AM »
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.
« Last Edit: February 12, 2018, 11:26:18 AM by Bern Ertl »

cj

  • Master Geek
  • ****
  • Posts: 626
    • View Profile
Re: Group by Questions
« Reply #7 on: February 12, 2018, 03:26:54 PM »
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