• Welcome, Guest. Please login.
 
August 20, 2019, 05:46:19 am

News:

Welcome to the SQLitening support forums!


Grouping and Summing Like Items

Started by Fredrick Ughimi, May 25, 2019, 08:52:21 pm

Previous topic - Next topic

Fredrick Ughimi

May 25, 2019, 08:52:21 pm Last Edit: May 26, 2019, 04:05:19 am by Fredrick Ughimi
Hello,

I want to group and sum like items. For instance, from the list below:

Cash 1,500.00
Cash 2,500.00
Account Receivable 2,000.00
Account Receivable 3,000.00
Interest Receivable 4,500.00

I  want to get:

Cash 4,000.00
Account Receivable 5,000.00
Interest Receivable 4,500.00

using SQL statement. Grouping and summing like items.

I tried:

slSel "Select * From tblJournalEntry WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Equity' GROUP BY AccountName HAVING SUM(Debit)"

Its grouping, but did not summing. The summing part is where I am having issues.
         
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

May 26, 2019, 10:55:19 am #1 Last Edit: May 26, 2019, 10:57:10 am by cj
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 slopen "junk.db3","C"
 slexe "create table if not exists t1(Acct,Amt)"
 slexe "insert into  t1 values ('Rec',3000)"
 slexe "insert into  t1 values ('Cash',2500)"
 slexe "insert into  t1 values ('Interest',4500)"
 slexe "insert into  t1 values ('Rec',2000)"
 slexe "insert into  t1 values ('Cash',1500)"
 viewall "select acct,sum(amt) from t1 group by acct"
END FUNCTION[

FUNCTION Viewall(sql AS STRING) AS STRING
 LOCAL sarray() AS STRING
 slselary sql,sarray(),"Q9"
 ? JOIN$(sarray(),$CR),,"Totals
END FUNCTION

Fredrick Ughimi

Thanks CJ,

My SQL Statement worked with your code as well. It seems to have issues without using the array.

slSel "Select AccountName, AccountType, Date, SUM(Credit) From tblJournalEntry WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Equity' GROUP BY AccountName"
          Do While slGetRow()
              slFN("AccountName") + $Tab + _   
              Format$(Val(slFN("Credit")), "#,.00") 
          Loop

I get invalid column Credit.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Never mind. I completely forgot this:

slSel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND AccountType = 'Equity' GROUP BY AccountName"
          Do While slGetRow()
              slFN("AccountName") + $Tab + _   
              Format$(Val(slFN("CreditSUM")), "#,.00") 
       
          Loop
   
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

Here is some code to eliminate rounding errors.
Other people and myself have seen it happen so we use INTEGERs for money.
This code also eliminates concatenation using CHR$, JOIN$ and REDIM PRESERVE
Did not modify sql in first results so the amounts are 100 times larger than the penny method in the second results.
Also uses printf('%.2f',SUM(Credit*.01)) as CreditSUM  in the second results instead of FORMAT$ or USING$.

There is nothing wrong with your code, just putting out alternatives.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 LOCAL s AS STRING,x,y AS LONG
 slOpen "junk.db3","C"
 slexe "drop table if exists tblJournalEntry"
 slexe "create table if not exists tblJournalEntry(AccountName,AccountType,Date,CreditINTEGER)"

 slexe "begin exclusive" 'lock database for dramatic speed increase
 FOR x = 1 TO 3          'need sample data to sum and group so inserting here using binding
  FOR y = 1 TO 3
   s=CHR$(    slBuildBindDat("klume"+FORMAT$(y),"T"),_ 'accountname
              slBuildBindDat("cash","T"),            _ 'accounttype
              slBuildBindDat("2019-05-28","T"),      _ 'date
              slBuildbindDat(STR$(y),"i")   )          'credit in pennies
   slexebind "insert into tblJournalEntry values(?,?,?,?)",s
  NEXT
 NEXT
 slexe "end"

 slsel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry GROUP BY AccountName"

 REDIM sArray(1 TO 99999) AS STRING
 RESET x
 DO WHILE slGetRow()
  INCR x
  sArray(x) = slFN("AccountName") + $TAB + FORMAT$(VAL(slFN("CreditSUM")), "#,.00")    'original
  'sArray(x)=CHR$(slFN("AccountName"),$TAB,USING$("#,.##",VAL(slFN("CreditSUM"))*.01)) 'using pennies
 LOOP
 REDIM PRESERVE sArray(1 TO x)
 ? CHR$( JOIN$(sArray(),$CR),$CR,$CR +_ 'slGetRow results followed by slSelAry results with printf
   viewall("select accountname as acct,printf('%.2f',SUM(Credit*.01)) as CreditSUM from tblJournalEntry group by accountname"))
END FUNCTION

FUNCTION viewall(sql AS STRING) AS STRING
 LOCAL sarray() AS STRING
 slSelAry sql,sarray(),"Q9c"
 FUNCTION = JOIN$(sarray(),$CR)
END FUNCTION

Fredrick Ughimi

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet