subquery uses ungrouped column "i.date_time" from outer query

Question

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.

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:

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

this gives me

Shipment Date  |   TOTAL Items
------------------------------
09/02/2014     |   4
09/01/2014     |   23

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'.

I'm looking for this:

 Shipment Date  |   TOTAL Items  |  Inventory   |  Ordered 
 ---------------------------------------------------------
 09/02/2014     |   4            |        3     |      1
 09/01/2014     |   23           |       20     |      3

Here is what im trying, but getting the 'subquery uses ungrouped column "i.date_time" from outer query' error

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

Answer 1

I think you just need conditional aggregation:

select to_char(date_time, 'MM-DD-YYYY') as "Shipment Date", count(*) as "TOTAL Items",
       sum(case when i.ordertype = 'ORDER' then 1 else 0 end) as NumOrders,
       sum(case when i.ordertype = 'INVENTORY' then 1 else 0 end) as NumInventory
from item_status_log il join
     items i
     on il.itemid = i.itemid
where old_status = 'ONORDER' 
group by "Shipment Date" 
order by "Shipment Date" desc;

Answer 2

Try:

select to_char(date_time, 'MM-DD-YYYY') as "Shipment Date",
       count(*) as "TOTAL Items",
       sum(case when ordertype = 'INVENTORY' then 1 else 0 end) as "Inventory",
       sum(case when ordertype = 'ORDER' then 1 else 0 end) as "Ordered"
  from item_status_log i
 where old_status = 'ONORDER'
 group by "Shipment Date"
 order by "Shipment Date" desc

Answer 3

In my case (Postgres 11), casting data types confused Postgres.

The outer query had a character varying date column, which I was casting to date in the query. That confused Postgres and it would refuse to refer to that grouped date column from a subquery. I had to change the data type of the grouped column in the table itself and use that in the query, which worked fine.

Before:

SELECT
     outer_tbl.my_varchar_date::date,
     :
     (SELECT .. FROM inner_table
      WHERE outer_tbl.my_varchar_date::date >= inner_tbl.some_date)
FROM
     outer_tbl
GROUP BY outer_tbl.my_varchar_date::date;

After:

SELECT
     outer_tbl.my_date_date,
     :
     (SELECT .. FROM inner_table
      WHERE outer_tbl.my_date_date >= inner_tbl.some_date)
FROM
     outer_tbl
GROUP BY outer_tbl.my_date_date;