SQL Server query question (Count maybe?)

I have the following query:

SELECT  A.shipment_id
        ,B.box_id
        ,A.shipment_status
FROM shipments A
join boxes B on A.shipment_id = B.shipment_id
where A.shipment_status = 2
Group by B.box_id, A.shipment_id, A.shipment_status

That returns a result set that looks like this:

shipment_id, box_id, shipment_status

101, boxA, 2

101, boxB, 2

101, boxC, 2

开发者_如何学Python 102, box101, 2

102, box102, 2

103, boxA1, 2

103, boxA2, 2

I would like to return something like this instead (showing a total amount of boxes per shipment):

shipment_id, box count, shipment_status

101, 3, 2

102, 2, 2

103, 2, 2

How would I achieve this?

Thanks!

SELECT  A.shipment_id
        ,COUNT(*) AS boxcount
        ,A.shipment_status
FROM shipments A
join boxes B on A.shipment_id = B.shipment_id
where A.shipment_status = 2
Group by A.shipment_id, A.shipment_status

Just need to remove the box_id from the GROUP BY and use COUNT, as you said in your title.

Try this:

SELECT  A.shipment_id
    , count(1)
    , A.shipment_status
  FROM shipments A
  join boxes B on A.shipment_id = B.shipment_id
 where A.shipment_status = 2
 Group by A.shipment_id, A.shipment_status