I would like to pull a report that will add another column to an existing table. I have a table that has two columns ShipmentID and ItemID. ItemID values are always unique, but ShipmentIDs will repeat because different Items can be in the same shipment. I want to run a query that will add a column to the existing table that will show how many unique items are in that specific shipmentID.

DB Table: Shipment_INFO

Ideal output.

I believe I need to create a temporary table with ShipmentID and the associated counts using:

select shipmentID, count(*)
from Shipment_INFO
group by shipmentID

and then join this table to the original Shipment_INFO table to get the output I am looking for. I could look into how to create temporary tables, but would like to know if there is a way to do this without a temp table.

>Solution :

One way to do it is with nested joins.

And you already have what you are looking for:

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