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.

Ankur A3 avatar image Ankur A3 commented ·

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)
Jordan Johnson avatar image Jordan Johnson ♦♦ Ankur A3 commented ·

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.