topic SQL query to get the average value of a field in FlexSim Forum

SQL query to get the average value of a field

patrickABAWF — Thu, 04 Nov 2021 21:11:57 GMT

[ FlexSim 21.2.4 ]

I've been trying for hours to write a very simple SQL query but I can't seem to find the correct synthax.

I have a List called "FinishedGoods" and I want to get the average of the field "Leadtime" and return it as a double avglt.

I wrote this but it doesn't work. I get "syntax error, unexpected '[', expecting end of code" in the Compiler Console.

Table result = Table.query("SELECT Leadtime FROM FinishedGoods \
AVG[Leadtime]");

double avglt = result[1]["Leadtime"];

What's wrong? I can't see it.

Thanks a lot,

Re: SQL query to get the average value of a field

Matthew_Gillespie — Thu, 04 Nov 2021 22:07:24 GMT

You need to put the AVG() function in the SELECT statement and use parentheses, not brackets:

SELECT AVG(Leadtime) FROM FinishedGoods

You can then return the result with result[1][1]

Table result = Table.query("SELECT AVG(Leadtime) FROM FinishedGoods");
return result[1][1];

You can also rename that column with an AS statement

Table result = Table.query("SELECT AVG(Leadtime) AS Leadtime FROM FinishedGoods");
return result[1]["Leadtime"];

Re: SQL query to get the average value of a field

patrickABAWF — Fri, 05 Nov 2021 12:32:02 GMT

Thanks this works. Also, if I want to get 3 things from the data, can I do them in the same query or do I have to do 3 queries as I did below which works fine?

Could I do it more elegantly by doing the 3 queries within the first one:

Table result = Table.query("SELECT AVG(Leadtime) FROM FinishedGoods");
AvgLeadTime = result[1][1];

Table result2 = Table.query("SELECT MIN(Leadtime) FROM FinishedGoods");
MinLeadTime = result2[1][1];

Table result3 = Table.query("SELECT MAX(Leadtime) FROM FinishedGoods");
MaxLeadTime = result3[1][1];

Thanks,

Re: SQL query to get the average value of a field

patrickABAWF — Fri, 05 Nov 2021 12:38:03 GMT

Forget my comment. I found the more elegant way:

Table result = Table.query("SELECT AVG(Leadtime), MIN(Leadtime), MAX(Leadtime) FROM FinishedGoods");
AvgLeadTime = result[1][1];
MinLeadTime = result[1][2];
MaxLeadTime = result[1][3];

I'm starting to get the hang of this SQL thing and loving it !!!

Thanks Matthew !