question

Przemyslaw Pasich avatar image
0 Likes"
Przemyslaw Pasich asked Jeanette F answered

SQL mixed datatypes when using GROUP BY and ORDER BY

When running a SQL query that aggregates over a field using GROUP BY and that field features a mix of datatypes (string and numerical), the results of the query are unexpected:

sql-mixed-datatypes-with-groupby.png

On the right there is only one datatype (string) in 'type' column and the query results are fine; however, on the left where there is a mix (string + num), the resulting query overwrites the value 'a' with a 0.

Is this a bug or am I misunderstanding how SQL works?

The model that recreates this behavior is attached:

SQL_mixed_datatypes_with_GROUPBY.fsm

FlexSim 24.1.0
sqlcalculated tableflexql
sql-mixed-datatypes-with-groupby.png (134.2 KiB)
sql-mixed-datatypes-with-groupby.fsm (32.7 KiB)
sql-mixed-datatypes-with-groupby.fsm (32.7 KiB)
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

1 Answer

Jeanette F avatar image
0 Likes"
Jeanette F answered

Hello @Przemyslaw Pasich,

The columns need to be the same data type. If the column is all strings then the query works as expected.

5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

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