question

Patrick Cloutier avatar image
0 Likes"
Patrick Cloutier asked Patrick Cloutier commented

SQL query to get the average value of a field

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,

FlexSim 21.2.4
sql queries
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

1 Answer

·
Matthew Gillespie avatar image
0 Likes"
Matthew Gillespie answered Patrick Cloutier commented

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"];
· 2
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Patrick Cloutier avatar image Patrick Cloutier commented ·

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,


0 Likes 0 ·
Patrick Cloutier avatar image Patrick Cloutier commented ·

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 !

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.