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

Question

question

Ankur A3 asked May 4, '22 Jordan Johnson commented May 9, '22

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?

Software Version:

FlexSim 21.0.10

query sql join combine

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 · 2022-05-05T22:48:54Z

Jordan Johnson answered May 5, '22 Jordan Johnson edited May 5, '22

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

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

Answer 2 · 2022-05-06T07:13:15Z

Ankur A3 answered May 6, '22 Jordan Johnson commented May 9, '22

Thank you so much! @Jordan Johnson

· 2

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.

Ankur A3 commented · May 09, 2022 at 02:54 PM

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:

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 ·

1652107840521.png (4.9 KiB)

Jordan Johnson ♦♦ Ankur A3 commented · May 09, 2022 at 04:13 PM

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 ·

transposedemo.fsm (32.9 KiB)

question