Hello all,
If I have a table of orders (order_id, sku,qty) how can I get the number of order lines in another column in the same table? Something like a subtotal.
Thank you.
order-lines.jpgRegards,
You can query the global table and count the rows.
Howto query with the clause count, sum, avg on w3schools . com
If you want to sum the values of several rows, than you put the sum clause in your query.
The result table consists of one cell. If you read the cell [1][1] after the table query directly, you can assign the value to a label or another cell in your global table. Perhaps you store the value in a local variable and assign the value later in the code to a cell in your table.
@Jörg Vogel
Hi,
I made a small script like this. The first query works fine to find the number of order lines/order just like you said. I would also like to find the total number of orders using DISTINCT but it's not working. Can you help ? Thanks.
Table orders = Table("orders");
for (int i = 1; i <= orders.numRows; i++) {
Table result_lines = Table.query("SELECT COUNT(order_id) FROM $1 WHERE order_id=$2" ,orders,orders[i][1]);
orders[i][6]=result_lines[1][1];
}
for (int i = 1; i <= orders.numRows; i++) {
Table result_orders = Table.query("SELECT COUNT(DISTINCT order_id) FROM $1",orders);
token.nr_of_orders=result_orders[1][1];
}
You find a list of SQL clauses that FlexSim supports at the end of this article. One user asked in the past to add DISTINCT to the SQL features in FlexSim. It isn't part of FlexSim so far.
11 People are following this question.
