title: Combine two queries with count function for a single result set
body: <p>I have two tables in a <code>mySQL</code> database</p>
  
  <pre><code>Table 1 [Orders]
  DateOpened (date), 
  revenue
  
  Table 2 [Movements]
  DateStarted (date), 
  DateStopped (date)
  </code></pre>
  
  <p>For any given date, there could be 0 or more entries in Table 1 and for Table 2, there could be 0 or more entries for both <code>DateStarted</code> or <code>DateStopped</code> (both DateStarted and DateStopped could be the <strong>same</strong> day or <strong>different</strong>)</p>
  
  <p>I want to query these tables for a date range (eg: a month) and get a result set for each date that has some data in this form:</p>
  
  <pre><code>date, sum(revenue), num_orders (count of orders for the date), num_movements (count of movements for the date)
  </code></pre>
  
  <p>I have created two separate queries which accomplish each part but I can't figure out how to combine them so that I can achieve a single efficient query and not have to merge the result sets in code.  The thing I seem to be getting hung up on is the count() function applying correctly.</p>
  
  <p>Here are the working existing queries:</p>
  
  <pre><code>select count(*) as total_movements, movement_date
  from (
      select dropoffdate as movement_date
      from bb_movement
      where (dropoffdate &gt;= '2013-01-01' and dropoffdate &lt;= '2013-06-30') 
      union all
      select pickupdate as movement_date
      from bb_movement
      where (pickupdate &gt;= '2013-01-01' and pickupdate &lt;= '2013-06-30')
  ) as t3
  group by movement_date
  order by movement_date asc
  
  select count(*) as num_orders, sum(order_amount) as revenue, dateopened as date_opened
  from bb_order
  where (dateopened &gt;= '2013-01-01' and dateopened &lt;= '2013-06-30') 
  group by dateopened
  order by dateopened asc
  </code></pre>
  
  <p>CREATE TABLE IF NOT EXISTS <code>bb_movement</code> (
    <code>movementid</code> int(10) unsigned NOT NULL AUTO_INCREMENT,
    <code>orderid</code> int(10) unsigned NOT NULL COMMENT
    <code>dropoffdate</code> date NOT NULL,
    <code>pickupdate</code> date NOT NULL )</p>
  
  <p>CREATE TABLE IF NOT EXISTS <code>bb_order</code> (
    <code>orderid</code> int(10) unsigned NOT NULL AUTO_INCREMENT,
    <code>movementid</code> int(10) unsigned NOT NULL,
    <code>dateopened</code> date NOT NULL,
    <code>order_amount</code> float NOT NULL DEFAULT '0' 
  }</p>