Jump to content

Need help with a specific ExecuteSQL statement


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

Recommended Posts

Howdy all,

 

I could ask this in Stack Overflow or similar but it seems FM SQL has certain limitations when creating more complex requests (joins, group by, etc).

 

I have a table of sales figures (location_id, date, amount).

 

Assuming there's only one location, and I want to graph the sales over time...

 

x-axis:

ExecuteSQL("SELECT date FROM sales_figures ORDER by date ASC") - I'd get a list of all the dates

 

y-axis:

ExecuteSQL("SELECT amount FROM sales_figures ORDER by date ASC") - I'd get a list of all the amounts

 

The problem is multiple locations are in the list and I want the graph to show sales data for one or more locations that I select. For the x-axis (dates) I can SELECT distinct... easy.

 

 

But what about the sales figures on the y-axis? I only want ONE amount for each date... So I want to start with the first date, find all the locations for that date, sum up their sales amounts, and then move onto the next date.

 

Any ideas on how to form an ExecuteSQL statement to achieve that?

Link to post
Share on other sites
Ah... thanks... Your suggestion was good for all locations, but what if I just want one selected location, or 2, or all?
 
I ended up doing this:
 
Let (
 
location_list = Substitute ( dashboard::location_ids ; "¶" ; " OR location_id =" );
 
ExecuteSQL ( "SELECT SUM(amount) FROM sales_figures WHERE (location_id = " & location_list & ") GROUP BY date_start ORDER BY date_start"; ""; "" )
 
)
Link to post
Share on other sites

what if I just want one selected location, or 2, or all?

I ended up doing this:

 

Try IN:

Let (
  locationIDs = Substitute ( dashboard::location_ids ; ¶ ; "," ) ;
  ExecuteSQL ( "
    SELECT SUM ( amount ) 
    FROM sales_figures 
    WHERE location_id IN (" & locationIDs & ")  
    GROUP BY date_start 
    ORDER BY date_start 
    "; "" ; "" 
  )
)
Link to post
Share on other sites

This topic is 2036 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

×
×
  • Create New...

Important Information

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