title: Distinct and sum aggregation in Spark using one command
body: <p>I am doing some Spark training and are wondering about optimizing one of my tasks. The goal is simple: calculate distinct number of orders and total order value by order date and status from the following table:</p>
  
  <p><a href="https://i.stack.imgur.com/nOWvM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nOWvM.png" alt="Input table"></a></p>
  
  <p>This has to be done in Spark's Dataframe API (Python or Scala), <strong>not</strong> SQL.</p>
  
  <p>In SQL, it would be simple:</p>
  
  <pre><code>select order_status, order_date, count(distinct order_item_id), sum(order_item_subtotal) 
  from df 
  group by order_status, order_date
  </code></pre>
  
  <p>The only way I could make it work in PySpark is in three steps:</p>
  
  <ol>
  <li><p>Calculate total orders</p>
  
  <p><code>df_to = df.groupby('order_date','order_status') \
       .agg(countDistinct(df.order_id) \
       .alias('total_orders'))</code></p></li>
  <li><p>Calculate distinct order item id</p>
  
  <p><code>df_ta = df.groupby('order_date','order_status') \
       .sum('order_item_subtotal') \ 
       .alias('total_amount') \
       .withColumnRenamed("sum(order_item_subtotal)","total_amount")</code></p></li>
  <li><p>Join tables above</p>
  
  <p><code>dfout = df_to.join(df_ta, [df_to.order_date == df_ta.order_date, df_to.order_status == df_ta.order_status], 'inner').select(df_to.order_date, df_to.order_status, df_to.total_orders, df_ta.total_amount)</code></p></li>
  </ol>
  
  <p>Is there a smarter way? Thank in you in advance!</p>