I have a table that contains items sold over the last 20 years, I would like to run a query where I can see a comparison of sales by year. 

 

Customer#   Name   Year1Sum   Year2Sum

1      ABC Company   5000.00   1000.00

2     BAC Company   2500.00   7500.00

 

I can get the first column SUM to work ok - how do I add the second column in the same results from different records.?

I would use the Virtual List technique, so I can loop by company and ExecuteSQL twice to get the two different years' data.

Why would you want to use ExecuteSQL() here, rather than summary fields?

Ok so here is my formula.  Works as a single SELECT statement - how can I get results to be output together Sum of dollars shipped from 2015 and 2016 on the same line.  Records are individually created from invoicing.

 

      Let ( [

           $FiscalYrA = globalFiscalYear;
           $FiscalYrB = $FiscalYrA - 1;
           xTab = "    ";

           $SQLResult = ExecuteSQL ( "SELECT \"Customer Number\", \"Name\", SUM (\"Dollars Shipped\"
                                         FROM Invoices
                                            LEFT JOIN Customer_File ON \"Customer Number\" = \"CustomerNo\"
                                               WHERE FiscalYear = ? AND \"Invoice Number\" <> 000000
                                                  GROUP BY \"Customer Number\", \"Name\"
                                     UNION

                                      SELECT \"Customer Number\", \"Name\", SUM (\"Det Dol Shipped\"
                                         FROM Invoices
                                            LEFT JOIN Customer_File ON \"Customer Number\" = \"CustomerNo\"
                                               WHERE FiscalYear = ? AND \"Invoice Number\" <> 000000
                                                  GROUP BY \"Customer Number\", \"Name\"

                                                      ORDER BY \"Customer Number\"" ; xTab; "¶" ;

                                     /*Misc Items*/ $FiscalYrA; $FiscalYrB );

           $xCount = ValueCount ( $SQLResult )

             ] ;
                   $Var )

 

This all just seems so simple and efficient compared to a sub-summary export to Excel that takes hours.

 

Edited February 12, 20169 yr by Mike E

1. UNION implies different lines. but there are tricks to have the "records" not use the return for each result row

2. I don't know if nested SELECTS here would work. SELECT field, SELECT(), SELECT()...

3. if you can get values (even two different select statements), you may be able to combine them using FM's text functions. Try changing the column and row separators with something easy to "parse".

beverly