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:
- 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/