AnswerHub Upload Issue
Our AnswerHub community currently has a bug that prevents you from attaching your model or other files to your questions. AnswerHub is aware of this issue and they are working on a fix. Until we receive their patch, you can upload your models and other files to FlexSim's file share site. Include the file name in your question so that we know which upload to reference.

question

Ankur A3 avatar image
0 Likes"
Ankur A3 asked Jordan Johnson commented

Combine 2 count query in different column in 1 calculated table?

Hi Team,

I want to combine 2 query result in 1 calculated table column wise and want to do operation further on that.

I am able to get 2 query result row wise using QUERY 1 UNION QUERY2 as below:

ROW1 Value1

ROW2 Value2

But I would like to see as below:

Col1 Col2

Value1 Value2

Which command I need to use?

FlexSim 21.0.10
querysqljoincombine
5 |100000

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

Jordan Johnson avatar image
1 Like"
Jordan Johnson answered Jordan Johnson edited

Combining tables "horizontally" in the way you ask is done with a join. Usually, the tables involved would both contain a key column. If two tables have an ID column, you can combine them using a regular join, making sure to match that key:

SELECT Col1, Col2 FROM Table1 AS t1 JOIN Table2 AS t2 ON t1.ID = t2.ID

Even if you don't have an ID column, I think you can use FlexSim's ROW_NUMBER to combine row N in table 1 with row N in table 2:

SELECT * FROM Table1 AS t1 JOIN Table2 AS t2 ON t1.ROW_NUMBER = t2.ROW_NUMBER

You can try it by running the script in this model:

RowJoinDemo.fsm


rowjoindemo.fsm (25.1 KiB)
5 |100000

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

Ankur A3 avatar image
0 Likes"
Ankur A3 answered Jordan Johnson commented

Thank you so much! @Jordan Johnson

· 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.

Hi @Jordan Johnson,

I was asking this question in 1 more context also. Suppose if I have to extract result in 1 calculated table based on 2 queries from same table instead of 2 separate table. For eg: I have global table as below:

1652107840521.png

In R1C1, SELECT COUNT(ID) FROM GlobalTable WHERE WeekDay=="Mon", i.e., 2

In R1C2, SELECT COUNT(ID) FROM GlobalTable, i.e., 8

2, 8

How to achieve this results in calculated table?

Thank you!

0 Likes 0 ·
1652107840521.png (4.9 KiB)

I think you can do that query without a join:

SELECT 
    COUNT(CASE WHEN WeekDay=="Mon" THEN ID END) AS Mon
  , COUNT(ID) AS AllDays
FROM GlobalTable1

TransposeDemo.fsm

0 Likes 0 ·
transposedemo.fsm (32.9 KiB)

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.