How to show two calculated columns to an existing table

I would like to pull a report that will add two columns to the first two columns of an existing table (Shipment_Info). Shipment_Info has three columns ShipmentID and ItemID and Item_Status. ItemID values are always unique, but ShipmentIDs will repeat because different Items can be in the same shipment, and item status can be in different states (Allocated, Filled and Packed)

I want to run a query that will add two column to the existing table (first two columns). The first new column (Shipment_Size) will show how many unique items there are in that specific shipmentID, and the 2nd new column (Shipment_ready) will show if the entire shipmentID is ready to be shipped. For an shipmentID to be ready to be ItemIDs must be in a "Packed" status. Any help would be greatly appreciated.

DB Table: Shipment_INFO

Ideal output.

Someone help me with getting the Shipment_Size using this query, but I'm struggling to figure out how to integrate the the Shipment_Packed column to the query below.

select s.shipmentID, s.ItemID, i.Shipment_Size 
from Shipment_INFO s 
  inner join ( select shipmentID, count(*) as Shipment_Size 
  from Shipment_INFO 
  group by shipmentID ) i on i.shipmentID=s.shipmentID`

Something like this should work

select
    shipmentid,
    itemid,
    count(*) over (partition by shipmentid) as shipment_size,
    case
        when (
            select count(*) from shipment_info si2
            where si2.shipmentid = si.shipmentid
            and si2.item_status = 'Packed'
        ) = count(*) over (partition by shipmentid)
        then 'Yes'
        else 'No'
    end as shipment_ready
from
    shipment_info si

or:

with
    shipment_status as (
        select
            shipmentid,
            count(1) as shipment_size,
            sum(case item_status when 'Packed' then 1 else 0 end) as packed_count
        from
            shipment_info
        group by
            shipmentid
    )
select
    si.shipmentid,
    si.itemid,
    ss.shipment_size,
    case ss.packed_count
        when ss.shipment_size then 'Yes'
        else 'No'
    as shipment_ready
from
    shipment_info si
    join
    shipment_status ss on ss.shipmentid = si.shipmentid

</details>



# 答案2
**得分**: 0

以下是您要翻译的代码部分：

```sql
select ShipmentID, ItemID,
       count(ItemID) over (partition by ShipmentID) as Shipment_Size,
       case when 
           sum(case when Item_status='Packed' then 1 else 0 end) OVER (partition by ShipmentID) = count(ItemID) over (partition by ShipmentID)
           then 'Yes' else 'no' end as Shipment_Ready
from Shipment_INFO
group by ShipmentID, Item_status, ItemID

请注意，上面的代码部分已经翻译为中文。

  select ShipmentID,ItemID,
           count(ItemID) over (partition by ShipmentID) Shipment_Size,
          case when 
      sum(case when Item_status='Packed' then 1 else 0 end) OVER (partition by ShipmentID ) =count(ItemID) over (partition by ShipmentID)
                   then 'Yes' else 'no' end as Shipment_Ready
      from Shipment_INFO
    group by ShipmentID,Item_status,ItemID

答案3

得分: 0

你想选择所有行，但对于每一行，要查看整个装运情况。为此，请使用带有OVER (PARTITION BY shipmentid)的分析函数：

select
  shipmentid,
  itemid,
  count(*) over (partition by shipmentid) as shipment_size,
  min(item_status) over (partition by shipmentid) = 'Packed' as shipment_ready
from shipment_info
order by itemid;

我在这里使用了一个小技巧。'Packed' 是按字母顺序的最后状态。因此，如果装运的最低状态是'Packed'，则表示其所有物品都已打包好，装运准备就绪。

select
  shipmentid,
  itemid,
  count(*) over (partition by shipmentid) as shipment_size,
  min(item_status) over (partition by shipmentid) = 'Packed' as shipment_ready
from shipment_info
order by itemid;