subquery uses ungrouped column from outer query

subquery uses ungrouped column from outer query ,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

3 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;


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

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;

See More: How to Undo a Git Add before git commit
          Completely remove MariaDB or MySQL from CentOS 7 or RHEL 7
          Add a column with a default value to an existing table in SQL
Source: https://stackoverflow.com/