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
| ShipmentID | ItemID |
|---|---|
| 10001 | 20001 |
| 10002 | 20002 |
| 10002 | 20003 |
| 10003 | 20004 |
| 10004 | 20005 |
| 10004 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
| 10005 | 20009 |
| 10006 | 20010 |
Ideal output.
| ShipmentID | ItemID | Shipment_Size |
|---|---|---|
| 10001 | 20001 | 1 |
| 10002 | 20002 | 2 |
| 10002 | 20003 | 2 |
| 10003 | 20004 | 1 |
| 10004 | 20005 | 3 |
| 10004 | 20006 | 3 |
| 10004 | 20007 | 3 |
| 10005 | 20008 | 2 |
| 10005 | 20009 | 2 |
| 10006 | 20010 | 1 |
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