SQL distinct COUNT and GROUP BY aggregation

[rhramac]

[ FlexSim 22.2.3 ]

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

[jason_lightfootVL7B4 · Accepted solution]

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.

[rhramac]

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

[jason_lightfootVL7B4]

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