SQL Header Group by Query

Question

question

Andres asked Jun 10, '24 Andres commented Jun 12, '24

SQL Header Group by Query

Hi,

I am making a query to get the best item type product based on certain conditions that must match. The problem is that I am not able to get the row headers with the item type that I am grouping by.

Is there a way to do this?

SQL Group By Example Headers.fsm

Here is the model.

In this example I want the final answer to be "30"

Software Version:

FlexSim 24.1.0

sql queries group by

1718059956433.png (82.3 KiB)

sql-group-by-example-headers.fsm (26.7 KiB)

5 |100000

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

Answer 1 · 2024-06-11T06:19:11Z

Felix Möhlmann answered Jun 11, '24 Andres commented Jun 12, '24

The result of the query is an internal data structure and doesn't have row headers, as far as I know. You will have to add the ModType column to the SELECT clause and treat it as its own column.

· 1

5 |100000

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

Andres commented · Jun 12 at 09:47 PM

You are right, that worked. Thanks

Table TableLineModules = Table.query("SELECT [ModType], SUM(CASE WHEN ([Zone] = 3 OR [Zone] = 4) then 1 else 0 END) as [Zone3&4], \

SUM(CASE WHEN ([Zone] = 5 OR [Zone] = 7) then 1 else 0 END) as [Zone5&7], \

COUNT(*) as [Total] \

FROM [Data] \

GROUP BY [ModType] \

ORDER BY [ModType] ASC ");

TableLineModules.cloneTo(Table("TableQuery1"));

0 ·

question