Jump to content
Sign in to follow this  
truelifeajf

Need help with a specific ExecuteSQL statement

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?

Share this post


Link to post
Share on other sites

Perhaps something like this...

ExecuteSQL ( "SELECT "date", SUM(amount) FROM sales_figures GROUP BY "date" ORDER BY "date"", '|', char (13) )

Tim

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

Well what I implemented with Tom's help has worked.

 

I guess "IN" is another method that will work too, though I haven't tested.

 

Thanks guys!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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