Jump to content

ExecuteSQL SUM of Sales Year1 & Year2


Mike E
 Share

This topic is 2320 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.?

Link to comment
Share on other sites

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 by Mike E
Link to comment
Share on other sites

  • 2 weeks later...

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

 

 

Link to comment
Share on other sites

This topic is 2320 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.