Add group by as an additional field
I am looking to add a column to a dataset where I join all my users and their orders (could be 1 or more) and get a count of how many orders an individual user has placed. I pulled all the columns I want in my ETL dataflow, and then calculated this field using the "Group By" transformation. I group by 'user_id' and aggregate my order_id's as a count.
This gets me the right data, but then the only columns I am left with are the 2 from the Group By transformation, with all the others dropped (i.e., name, email, city, order info, etc.). Is there a way to get the counts of each user's total orders and add this as an additional column where I have 1 row per distinct user_id? I tried both an ETL dataflow and a SQL transform but nothing worked. I can get the exact data I want going directly to the SQL database, but would like to do this directly in Domo and get a card created/updated automatically.
Thanks for the help!!