I demonstrated how to sum groups and nested groups using GROUP BY ... and WITH ROLLUP. There should be no problem for any one to extend this logic to any levels.
In your case, first digit indicates the major group, second digit the level2 group, 3rd and 4th digits level3 group and next 3 digits individual item code. Adopting the above logic, this is the sql:
UPDATE PROD_GRP S
LEFT OUTER JOIN (
SELECT COALESCE( A.GROUP3, A.GROUP2, A.GROUP1 ) AS PRODGROUP,
A.NUM,A.QTY, A.AMT
FROM
(
SELECT SUBSTRING( CODE, 1, 1 ) AS GROUP1,
SUBSTRING( CODE, 1, 2 ) AS GROUP2,
SUBSTRING( CODE, 1, 4 ) AS GROUP3,
COUNT( CODE ) AS NUM,
SUM( QTY ) AS QTY,
SUM( AMT ) AS AMT
FROM SALE_TRN
GROUP BY SUBSTRING( CODE, 1, 1 ),
SUBSTRING( CODE, 1, 2 ),
SUBSTRING( CODE, 1, 4 )
WITH ROLLUP
) A
) C
ON S.CODE = C.PRODGROUP
SET S.QTY = IFNULL( C.QTY, 0 ),
S.AMT = IFNULL( C.AMT, 0 ),
S.NUM = IFNULL( C.NUM, 0 )
This principle can be adopted to any different situations.
This is the revised test program.
#include "fivewin.ch"
#include "xbrowse.ch"
static oCn
//----------------------------------------------------------------------------//
function Main()
oCn := FW_OpenAdoConnection( <YOUR CONNECTION STRING> )
CreateProductsTable()
MsgRun( "0", "TRANSACTION TABLE", { |oDlg| CreateTransactions( oDlg ) } )
CreateSummary()
// CleanUP
oCn:Execute( "DROP TABLE PROD_GRP" )
oCn:Execute( "DROP TABLE SALE_TRN" )
oCn:Close()
return nil
//----------------------------------------------------------------------------//
static function CreateSummary()
local oRs, cSql
local nSecs
TEXT INTO cSql
UPDATE PROD_GRP S
LEFT OUTER JOIN (
SELECT COALESCE( A.GROUP3, A.GROUP2, A.GROUP1 ) AS PRODGROUP,
A.NUM,A.QTY, A.AMT
FROM
(
SELECT SUBSTRING( CODE, 1, 1 ) AS GROUP1,
SUBSTRING( CODE, 1, 2 ) AS GROUP2,
SUBSTRING( CODE, 1, 4 ) AS GROUP3,
COUNT( CODE ) AS NUM,
SUM( QTY ) AS QTY,
SUM( AMT ) AS AMT
FROM SALE_TRN
GROUP BY SUBSTRING( CODE, 1, 1 ),
SUBSTRING( CODE, 1, 2 ),
SUBSTRING( CODE, 1, 4 )
WITH ROLLUP
) A
) C
ON S.CODE = C.PRODGROUP
SET S.QTY = IFNULL( C.QTY, 0 ),
S.AMT = IFNULL( C.AMT, 0 ),
S.NUM = IFNULL( C.NUM, 0 )
ENDTEXT
nSecs := Seconds()
MsgRun( "Summarizing", "PROD_GRP", { || oCn:Execute( cSql ) } )
nSecs := Seconds() - nSecs
oRs := FW_OpenRecordSet( oCn, "PROD_GRP" )
xbrowser oRs TITLE "SUMMARY"
oRs:Close()
return nil
//----------------------------------------------------------------------------//
static function CreateProductsTable()
local oRs, cSql, i,j,k, c, aProd := {}
TRY
oCn:Execute( "DROP TABLE PROD_GRP" )
CATCH
END
TEXT INTO cSql
CREATE TABLE PROD_GRP (
CODE VARCHAR( 4 ) PRIMARY KEY,
LEVEL SMALLINT,
DESCRIPT VARCHAR( 20 ),
NUM INTEGER,
QTY DECIMAL( 14, 3 ),
AMT DECIMAL( 15, 2 )
)
ENDTEXT
oCn:Execute( cSql )
for i := 1 to 3
c := Str( i, 1, 0 )
AAdd( aProd, { c, 1, "Major Group - " + c } )
for j := 1 to 3
c := Str( i, 1, 0 ) + Str( j, 1, 0 )
AAdd( aProd, { c, 2, "Group - " + c } )
for k := 1 to 3
c := Str( i, 1, 0 ) + Str( j, 1, 0 ) + StrZero( k, 2, 0 )
AAdd( aProd, { c, 3, "Sub Group - " + c } )
next
next
next
cSql := ""
for i := 1 to Len( aProd )
if Empty( cSql )
cSql := "INSERT INTO PROD_GRP ( CODE, LEVEL, DESCRIPT ) VALUES "
else
cSql += ", "
endif
cSql += "( '" + aProd[ i, 1 ] + "', " + cValToChar( aProd[ i, 2 ] ) + ",'" + aProd[ i, 3 ] + "' )"
next
oCn:Execute( cSql )
oRs := FW_OpenRecordSet( oCn, "PROD_GRP" )
XBROWSER oRs TITLE "PRODUCT GROUPS"
oRs:Close()
return nil
//----------------------------------------------------------------------------//
static function CreateTransactions( oDlg )
local oRs, cSql, cVal, n
local nSecs
TRY
oCn:Execute( "DROP TABLE SALE_TRN" )
CATCH
END
TEXT INTO cSql
CREATE TABLE SALE_TRN (
ID INT AUTO_INCREMENT PRIMARY KEY,
CODE VARCHAR( 7 ),
QTY DECIMAL( 12, 3 ),
AMT DECIMAL( 16, 2 )
)
ENDTEXT
oCn:Execute( cSql )
nSecs := Seconds()
cSql := ""
for n := 1 to 10000
cVal := " ( '" + Chr( HB_RandomInt( 49, 51 ) ) + Chr( HB_RandomInt( 49, 51 ) ) + '0' + Chr( HB_RandomInt( 49, 51 ) ) + StrZero( HB_RandomInt( 1, 999 ), 3, 0 ) + "'"
cVal += ", " + cValToChar( Round( HB_Random( 1, 9000 ), 3 ) )
cVal += ", " + cValToChar( Round( HB_Random( 10, 90000 ), 2 ) )
cVal += " )"
if Empty( cSql )
cSql := "INSERT INTO SALE_TRN ( CODE, QTY, AMT ) VALUES " + cVal
else
cSql += ", " + cVal
endif
if n % nBatchSize == 0
oCn:Execute( cSql )
cSql := ""
if oDlg != nil
oDlg:cMsg := cValToChar( n ) + '/' + cValToChar( Seconds() - nSecs ) + " Secs"
oDlg:Refresh()
SysRefresh()
endif
endif
next
nSecs := Seconds() - nSecs
cSql := ""
oRs := FW_OpenRecordSet( oCn, "SALE_TRN" )
XBROWSER oRs TITLE + "10,000 TRANSACTIONS (" + cValToChar( nSecs ) + " Secs )"
oRs:Close()
return nil
//----------------------------------------------------------------------------//
