Count distinct values in ColumnA for each unique value in ColumnB

I have a table of shipment numbers (can be duplicate) and a concatenated field of their origin – destination (can be duplicate). I have a select column that selects all of the origin-destinations for each shipment number. I want to add a 3rd column that gets the number of unique origin-destinations for each unique shipment number.

I tried to do a

Select [OD], [ShipmentNo], [UniqueODs] = (COUNT([OD])) 

and also tried

 Select [OD], [ShipmentNo], [UniqueODs] =(COUNT([ShipmentNo]) 

instead of [OD]. Everything returns 1 in my additional column.

 SELECT     [OD],
            [ShipmentNo],
            [UniqueODs] = (COUNT([OD]))


             FROM tblShipments

             GROUP BY MVMT_NO, [OD Pair]

             ORDER BY MVMT_NO, [OD Pair]

Desired Results:

[Origin-Destination]                [ShipmentNo]             [UniqueODs]
Michigan-California                 A484310                  1
Michigan-California                 A484311                  1
Texas-California                    A484312                  2
Michigan-California                 A484312                  2

Current Results:

[Origin-Destination]                [ShipmentNo]             [UniqueODs]
Michigan-California                 A484310                  1
Michigan-California                 A484311                  1
Texas-California                    A484312                  1
Michigan-California                 A484312                  1

I want the output for A484312 on both lines to be 2, not 1.

Desired Results:

[OD]                    [ShipmentNo]              [UniqueODsForSN]
Michigan-California     A484310                   1
Michigan-California     A484311                   1
Texas-California        A484312                   2
Michigan-California     A484312                   2

Answer

I think you want a window function:

SELECT OD, ShipmentNo, SUM(COUNT(*)) OVER (PARTITION BY ShipmentNo) as UniqueODs
FROM tblShipments
GROUP BY MVMT_NO, [OD Pair]
ORDER BY MVMT_NO, [OD Pair];

Hmmm . . . if your current counts are 1, you don’t need aggregation:

SELECT OD, ShipmentNo, COUNT(*) OVER (PARTITION BY ShipmentNo) as UniqueODs
FROM tblShipments
ORDER BY MVMT_NO, [OD Pair];

EDIT:

If think you actually want:

SELECT OD, ShipmentNo, COUNT(*) OVER (PARTITION BY ShipmentNo) as UniqueODs
FROM tblShipments
GROUP BY MVMT_NO, [OD Pair]
ORDER BY MVMT_NO, [OD Pair];