title: Change Groupby with Join Spark SQL query to Spark Dataframe
body: <p>I have written my scripts initially using Spark SQL but now for performance and other reasons trying to convert the Sql queries to PySpark Dataframes.</p>
  
  <p>I have <code>Orders</code> Table <code>(OrderID,CustomerID,EmployeeID,OrderDate,ShipperID)</code>
  and <code>Shippers</code>  Table <code>(ShipperID, ShipperName)</code></p>
  
  <p>My Spark SQL query lists the number of orders sent by each shipper:</p>
  
  <pre><code> sqlContext.sql("SELECT Shippers.ShipperName, COUNT(Orders.ShipperID) AS NumberOfOrders 
   FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID 
   GROUP BY ShipperName")
  </code></pre>
  
  <p>Now when i try to replace the above SQL query with Spark Dataframe,i write this</p>
  
  <pre><code>Shippers.join(Orders,["ShipperID"],'left').select(Shippers.ShipperName).groupBy(Shippers.ShipperName).agg(count(Orders.ShipperID).alias("NumberOfOrders")) 
  </code></pre>
  
  <p>But I get an error here mostly because i feel aggregate count function while finding count of orderId from Orders table is wrong.</p>
  
  <p>Below is the error that i get:-</p>
  
  <pre><code>"An error occurred while calling {0}{1}{2}.\n".format(target_id, ".", name), value)"
  </code></pre>
  
  <p>Can someone please help me to refactor the above SQL query to Spark Dataframe ?</p>