9 Replies Latest reply on Apr 13, 2016 10:17 PM by user19752

    SQL Arrays aggregation

    iconomydk

      Goal to see total sales for item group in time period

      Problem I have 2 different line-item tables which uses same item group

       

      With two SQL statements I get the following (Itemgroup no. | Sales | Profit)

       

      $Table 1

      5|100|25

      6|125|30

      7|150|40

       

      $Table 2

      5|50|10

      6|70|20

      7|60|30

       

      now I would like to aggregate these 2 arrays into:

       

      5|150|35

      6|195|50

      7|210|70

       

      How can this be done ?


      Thanks Lars

        • 1. Re: SQL Arrays aggregation
          mikebeargie

          Start here, I would read the entire page and all of the comments.

          https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

           

          To add two tables, you will need a JOIN between the two. Then you will need to use math for the addition.

           

          Something like this would be what you are looking for:

          ExecuteSQL("SELECT a.itemgroup, a.sales + b.sales AS salesTotal, a.profit + b.profit AS profitTotal FROM yourTable a JOIN yourSecondTable b ON a.itemgroup = b.itemgroup WHERE ... your conditions..." ; "" ; "" )

          Note that you alias the first table as a, then all the fields from that table are a.fieldName, and the same for your second table as b.

          • 2. Re: SQL Arrays aggregation
            beverly

            I see Mike B. beat me to the answer. Yes, I would JOIN and use simple addition.

             

            If you need something "sorted", then add:

            ORDER BY a.itemgroup

            after the WHERE.

             

            You have "|" pipe -delimited, so

            ExecuteSQL ( " SELECT

            ...

            WHERE ....

            ORDER BY ..."

            ; "|" ; "" )

             

            beverly

            • 3. Re: SQL Arrays aggregation
              CamelCase_data

              It should be noted that FULL OUTER JOIN isn't currently supported, so you may run into issues if both tables do not always contain records from all of the ItemGroups. If one does, you can use LEFT JOIN from the table that always has records for all groups.

              • 4. Re: SQL Arrays aggregation
                beverly

                very true, David!  or Join from a Parent that might point to both Children? However, it just looks like two table with the same data (perhaps one an archive of the other). Lars will need to let us know if more information can refine the query.

                 

                beverly

                • 5. Re: SQL Arrays aggregation
                  iconomydk

                  Thanks for the different replies. And the link to filemakerhacks article. Very helpful and a bit overwhelming for an SQL “beginner”

                   

                  The reason for having two line-item tables is because my solution can handle normal invoice selling, but also POS selling. Users can sell items from the same items table, but invoicing and POS use different line-items tables.

                   

                  Each lineitem tables have the itemGroup and a date – and I have come up with SQL statements like this:

                   

                  Let(

                  [

                  date_from=Globals::date_from;

                  date_to = Globals::date_to

                  ];

                  ExecuteSQL ( "Select Sum (Line_total), sum(Line_profit), ID_item_group FROM POS_line_items Where POS_date >= ? and POS_date <= ? group by ID_item_group” ; "|" ; ""; date_from; date_to)

                  ) //Translated for easier understanding

                   

                  I have a similar SQL for the other Invoice_line_items Table

                   

                  The 2 SQL statements work nicely – but for me the tricky part is – how do I create this SQL statement that gives me the totals of both line-item ?? if possible.

                  • 6. Re: SQL Arrays aggregation
                    CamelCase_data

                    Do you have an ItemGroup table or similar that contains one record per Item Group with the unique Item Group IDs?

                    • 8. Re: SQL Arrays aggregation
                      beverly

                      See Mike's answer with the JOIN on the two tables.

                      beverly

                       

                      p.s. get the PDF and example files from the article. Much more to see!

                      • 9. Re: SQL Arrays aggregation
                        user19752

                        It is impossible or very difficult in one query since FM SQL don't allow calculation on aggregated result nor sub query in FROM.

                        You would need virtual list for put the two result into, or make unstored calculation on ItemGroup table.

                        Or, use custom function to merge list.