SQL distinct COUNT and GROUP BY aggregation

Question

question

Rahul R asked Feb 16, '23 Jason Lightfoot commented Feb 17, '23

SQL distinct COUNT and GROUP BY aggregation

Hi,

I am trying to transform the input table but finding it difficult without DISTINCT COUNT. I am assuming I will have to use GROUP BY and joins to accomplish this transformation. Any help is appreciated.

Input

Output:

Here is a simulation model with sample data

SQL_test1.fsm

Software Version:

FlexSim 22.2.3

sql queries sql query sql join distinct

1676585827918.png (31.3 KiB)

1676585851641.png (5.1 KiB)

sql-test1.fsm (28.3 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 · 2023-02-16T23:40:19Z

Jason Lightfoot answered Feb 16, '23 Jason Lightfoot commented Feb 17, '23

I'd probably write a script for this but you can get close in SQL:

Table.query("SELECT a.Task_id, a.Task_assigned_at, a.Type, b.numSKUs,c.numTotes FROM Input a JOIN \
(SELECT Task_id,COUNT(SKU) AS numSKUs FROM  (SELECT Task_id, SKU FROM Input GROUP BY Task_id, SKU)) b on b.Task_id=a.Task_id \
JOIN  (SELECT Task_id,COUNT(tote_id) AS numTotes FROM (SELECT Task_id, tote_id FROM Input GROUP BY Task_id, tote_id)) c  ON c.Task_id=a.Task_id GROUP BY a.Task_id").cloneTo("Output")

COUNT (DISTINCT SKU) would be simpler - you could suggest it in the ideas section.

All that's left is putting the function to convert the datetime string and find the minimum.

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

Rahul R commented · Feb 17, 2023 at 02:48 AM

@Jason Lightfoot -Thank you for the sample code. By script, do you mean to traverse through the global table and create the output table?

0 ·

Jason Lightfoot ♦♦ Rahul R commented · Feb 17, 2023 at 06:46 AM

Exactly - I'd probably use a nested Map type (a map containing other maps) to register the unique Skus and tote IDs for each taskid

0 ·

question