CODEWITHSUNDEEP
sqlsql-servert-sql
kimball
kimball

Reputation: 3

Group by result of count

First, this is with Microsoft SQL Server 2014

I've got two tables in a database, Shipment and Package. Each shipment has a Shipment_ID, and each package has both a Shipment_ID and a Package_ID. A shipment can be made up of more than one package, i.e. multiple packages can have the same Shipment_ID.

I'm trying to figure out the number of shipments I have with per amount of packages.

So, something like this:

Count Packages Number of Shipments
1 1,000,000
2 250,000
3 100,000
4 5,000
5 1,000

I've figured out how to get the number of packages for each Shipment_ID, which I've done like this:

SELECT s.Shipment_ID, COUNT(p.Package_ID)
FROM Shipment s
LEFT JOIN Package p ON s.Shipment_ID = p.Shipment_ID
GROUP BY s.Shipment_ID

This is where I'm stuck. I can't figure out how to consolidate this down to the number of shipments for each package count.

My first thought was to somehow group by COUNT(p.Package_ID), but aggregate functions can't be used in group by.

Does anyone have any idea how I can do this?

Upvotes: 0

Views: 89

Answers (1)

Dale K
Dale K

Reputation: 27202

Use your query as a sub-query and slap another query over the top.

SELECT ShipmentCount, COUNT(*)
FROM (
    SELECT s.Shipment_ID, COUNT(p.Package_ID) ShipmentCount
    FROM Shipment s
    LEFT JOIN Package p on s.Shipment_ID = p.Shipment_ID
    GROUP BY s.Shipment_ID
) X
GROUP BY ShipmentCount;

Upvotes: 1

Related Questions