Help with a Mysql query

[FranciscoA]

Rao wrote:
I just answered mr jbrita's requirement.
If you are talking about accounting systems, there are a lot of better ways to do.
We can straightaway get account summary ( some countries call it trial balance) with group totals and all in one stroke.
Then super-impose closing entries and display balance-sheet and profit and loss account.
I suggest you post a specific requirement in a separate thread and we see how simple the entire process can be.
It is a catalog of productos, like a chart of accounts. (Accounting)

This catalog of products is defined by groups, subgroups and detail, such as:

Code: Select all  Expand view

1   -  Sales of spirits in general     15,000.00
11 -  Sale of Foreign liquors         10,000.00
111 - Shivas Reagal                      6,000.00
112 - Jhonny Walker                    3,000.00
113 - etc ....                               1,000.00
12  - Sale of National Liquors        5,000.00
121 - Ron Flor de Caña                 3,000.00
122 - Ron Plata                            1,500.00
123 - Etc .....                                 500.00
 

As the above example, what I intend to do is a single UPDATE from another table of movements of products which are identified with the codes 111,112,113,121,122,123 (which in this case are the codes last level or detail).

I mean sum the values ​​in the table of movements, for each product, and then upgrade to the "catalog" table this values ​​that correspond to each specific product, and at same time, update the group and sub-group to which they relate, in only one query. (all in one stroke)

Thus, all product codes that start with 11 must be accumulated in the sub-group 11 and in Group 1.

In the same way, all product codes that start with 12, must be accumulated in the sub-group 12 and in Group 1.

This I have done since time ago, but in DBF.

Excuse my English. I used Google Traductor.
Thanks.

[FranciscoA]

Mr. Rao:
I have the following query, which works , but I have to repeat for each level .
How to do it in a single query ?

Code: Select all  Expand view

 UPDATE  catalogue
  INNER JOIN
  ( SELECT codicont, SUM(debe) sumadebe, SUM(haber) sumahaber FROM  movements GROUP BY SUBSTRING(codicont,1,1))  T2
  ON T1.codicont = SUBSTRING(t2.codicont,1,1)   SET T1.movdebe =  T2.sumadebe, T1.movhaber = T2.sumahaber;
 

Regards

[nageswaragunupudi]

If we need single level grouping we can use simple GROUP BY clause. For 2 or more levels of nested grouping we need to use "GROUP BY ... WITH ROLLUP" clasue.

This is an example of the sql which summarizes two levels of grouping and updates the summaries in the catalog by name PROD_GRP table.

Code: Select all  Expand view

  UPDATE PROD_GRP S
   LEFT OUTER JOIN (
      SELECT IFNULL( A.SUBGROUP, A.MAJORGROUP ) AS PRODGROUP, A.QTY, A.AMT
      FROM
      (
      SELECT SUBSTRING( CODE, 1, 1 ) AS MAJORGROUP, SUBSTRING( CODE, 1, 2 ) AS SUBGROUP,
             SUM( QTY ) AS QTY, SUM( AMT ) AS AMT
      FROM SALE_TRN
      GROUP BY SUBSTRING( CODE, 1, 1 ), SUBSTRING( CODE, 1, 2 ) WITH ROLLUP
      ) A
   ) C
   ON S.CODE = C.PRODGROUP
   SET S.QTY    = IFNULL( C.QTY, 0 ),
       S.AMT    = IFNULL( C.AMT, 0 )
 

You need to change table names and field names according to your requirements.

You may like to test this with some dummy data. Here is a sample. This sample creates a PROD_GRP table (which you referred to as catalog above) and a transaction table, SALES_TRN with 500,000 transactions.
In the function CreateSummary() the program summarizes the data and updates the catalog table PROD_GRP. On my PC it took less than 2 seconds.

Finally cleans up these test tables by deleting them.

Code: Select all  Expand view

#include "fivewin.prg"
#include "xbrowse.prg"

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" )

return nil

//----------------------------------------------------------------------------//

static function CreateSummary()

   local oRs, cSql
   local nSecs

   TEXT INTO cSql
   UPDATE PROD_GRP S
   LEFT OUTER JOIN (
      SELECT IFNULL( A.SUBGROUP, A.MAJORGROUP ) AS PRODGROUP, A.QTY, A.AMT
      FROM
      (
      SELECT SUBSTRING( CODE, 1, 1 ) AS MAJORGROUP, SUBSTRING( CODE, 1, 2 ) AS SUBGROUP,
             SUM( QTY ) AS QTY, SUM( AMT ) AS AMT
      FROM SALE_TRN
      GROUP BY SUBSTRING( CODE, 1, 1 ), SUBSTRING( CODE, 1, 2 ) WITH ROLLUP
      ) A
   ) C
   ON S.CODE = C.PRODGROUP
   SET S.QTY    = IFNULL( C.QTY, 0 ),
       S.AMT    = IFNULL( C.AMT, 0 )
   ENDTEXT

   nSecs       := Seconds()
   MsgRun( "Summarizing", "PROD_GRP", { || oCn:Execute( cSql ) } )
   nSecs       := Seconds() - nSecs

   oRs   := FW_OpenRecordSet( oCn, "PROD_GRP" )
   xbrowser oRs TITLE "SUMMARY (" + cValToChar( nSecs ) + "Secs )"
   oRs:Close()


return nil

//----------------------------------------------------------------------------//

static function CreateProductsTable()

   local oRs, cSql, i,j, c, aProd  := {}

   TRY
      oCn:Execute( "DROP TABLE PROD_GRP" )
   CATCH
   END

   TEXT INTO cSql
   CREATE TABLE PROD_GRP (
   CODE      VARCHAR( 2 ) PRIMARY KEY,
   DESCRIPT  VARCHAR( 20 ),
   QTY       DECIMAL( 14, 3 ),
   AMT       DECIMAL( 15, 2 )
   )
   ENDTEXT

   oCn:Execute( cSql )

   for i := ASC( '1' ) to ASC( '5' )
      c     := Chr( i )
      AAdd( aProd, { c, "Group - " + c } )
      for j := ASC( '1' ) to ASC( '5' )
         c     := Chr( i ) + Chr( j )
         AAdd( aProd, { c, "SubGroup - " + c } )
      next
   next

   cSql  := ""
   for i := 1 to Len( aProd )
      if Empty( cSql )
         cSql  := "INSERT INTO PROD_GRP ( CODE, DESCRIPT ) VALUES "
      else
         cSql  += ", "
      endif
      cSql  += "( '" + aProd[ i, 1 ] + "', '" + aProd[ i, 2 ] + "' )"
   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( 3 ),
   QTY   DECIMAL( 12, 3 ),
   AMT   DECIMAL( 16, 2 )
   )
   ENDTEXT

   oCn:Execute( cSql )

   nSecs    := Seconds()
   cSql     := ""
   for n := 1 to 500000
      cVal  := " ( '" + Chr( HB_RandomInt( 49, 53 ) ) + Chr( HB_RandomInt( 49, 53 ) ) + Chr( HB_RandomInt( 49, 57 ) ) + "'"
      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 + "500,000 TRANSACTIONS (" + cValToChar( nSecs ) + " Secs )"
   oRs:Close()

return nil

//----------------------------------------------------------------------------//
 

[FranciscoA]

Thanks, Mr. Rao.
I will try it tonight.
Regards

[FranciscoA]

Yes, your code works, but I am not able to adapt it to my needs.
I want to update more than 2 levels. eg:

Code: Select all  Expand view

CODE      DESCRIP         VALUES
1               Group              10,000.00
11             S_Group           4,000.00
1101         Major                4,000.00
1101001   Detail                3,000.00
1101002   Detail                1,000.00

12             S_Group           6,000.00
1201         Major                6,000.00
1201001   Detail                4,000.00
1201002   Detail                2,000.00
 

Thanks, anyway.

Maybe someone else, have a code that can adapt to my needs and wants to share it.

[nageswaragunupudi]

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:

Code: Select all  Expand view

  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.

Code: Select all  Expand view

#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

//----------------------------------------------------------------------------//
 

[FranciscoA]

Thank you, Mr. Rao .
Now I could adapt your code to my needs. I did not know about the SQL COALESCE ()
You're very kind.