SQL Server query question (Count maybe?)

Question

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
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!

Answer 1

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

Answer 2

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.