Need help with a specific ExecuteSQL statement

[truelifeajf]

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?

[timdietrich]

Perhaps something like this...

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

Tim

[truelifeajf]

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

 

)

[eos]

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

[truelifeajf]

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!