title: subquery uses ungrouped column "i.date_time" from outer query
body: <p>I have two tables: item_status_log and items. The items table has the columns itemid, status, and ordertype. The item_status_log table has itemid, date_time, new_status, and old_status. Basically, when the status is changed in my program, a record is logged in the item_status_log with the old status, the new status, and the date_time. </p>
  
  <p>What I want is to be able to view a table of items grouped by the date they were updated. I have the following sql which works perfect:</p>
  
  <pre><code>select to_char(date_time, 'MM-DD-YYYY') as "Shipment Date", count(*) as "TOTAL Items"
  from item_status_log i where old_status = 'ONORDER' 
  group by "Shipment Date" 
  order by "Shipment Date" desc
  </code></pre>
  
  <p>this gives me </p>
  
  <pre><code>Shipment Date  |   TOTAL Items
  ------------------------------
  09/02/2014     |   4
  09/01/2014     |   23
  </code></pre>
  
  <p>However, I want to add 2 column to the above table, which break down how many of the items have a status in the items table of 'INVENTORY' and 'ORDER'.</p>
  
  <p>I'm looking for this:</p>
  
  <pre><code> Shipment Date  |   TOTAL Items  |  Inventory   |  Ordered 
   ---------------------------------------------------------
   09/02/2014     |   4            |        3     |      1
   09/01/2014     |   23           |       20     |      3
  </code></pre>
  
  <p>Here is what im trying, but getting the 'subquery uses ungrouped column "i.date_time" from outer query' error</p>
  
  <pre><code>select to_char(date_time, 'MM-DD-YYYY') as "Shipment Date", count(*) as "TOTAL Items",
  (select count(*) from item_status_log t 
  where date(t.date_time) = date(i.date_time) and itemid in (select itemid 
  from items where  ordertype = 'ORDER')) as "Customer",
  (select count(*) from item_status_log t 
  where date(t.date_time) = date(i.date_time) and itemid in (select itemid 
  from items where  ordertype = 'INVENTORY')) as "Inventory"
  from item_status_log i where old_status = 'ONORDER' 
  group by "Shipment Date" 
  order by "Shipment Date" desc
  </code></pre>