Query to Loop Through Data and Provide Table for Each Unique Value

Question

I'm trying to write a query to loop through data from a specific table and return results for each type. I have somewhat of the logic figured out I think anyways I just know it wont work as I'm missing something.

The data I'm working with is structured like this:

        Customer ID     Delivery Type
    143             Delayed
    123             Delivered 
    14              In Process
    123             In Route
    432             Delivered 
    456             Delayed
    76              In Route
    34              In Route
    546             Delayed
    324             Delivered 
    235             In Process
    678             Delayed
    234             In Route
    123             In Route
    321             Delayed
    987             In Process
    546             In Process
    324             Delayed
    235             In Route
    678             In Process
    234             Delayed
    123             In Route
    321             Delivered 
    987             In Process

I'm trying to get a result like this:

Delivery Type   Customer ID  Count
Delayed         143             1
Delayed         234             1
Delayed         321             1
Delayed         324             1
Delayed         456             1
Delayed         546             1
Delayed         678             1

Delivery Type   Customer ID  Count
Delivered       123             1
Delivered       321             1
Delivered       324             1
Delivered       432             1

Delivery Type   Customer ID  Count
In Process      14              1
In Process      235             1
In Process      546             1
In Process      678             1
In Process      987             2

Delivery Type   Customer ID  Count
In Route        34              1
In Route        76              1
In Route        123             3
In Route        234             1
In Route        235             1

I started out with the following logic that I know won't work:

DECLARE @i int
DECLARE @DeliveryCount int
DECLARE @Type int

@type = Select distinct DeliveryType from Deliveries

@DeliveryCount = select count(*) as 'Count' from @type

SET @i = 0

WHILE @DeliveryCount > @i
BEGIN 
    @DeliveryType = Select row @i from @type

    @Top10 = SELECT TOP 10 count(*) as 'Count', CustomerID, DeliveryType FROM Deliveries
            Group by CustomerID, DeliveryType
            Where DeliveryType like @DeliveryType
    Print @Top10

    Set @i = @i + 1
END

Answer 1

You seem to want group by and order by:

select delivery_type, customer_id, count(*)
from deliveries
group by delivery_type, customer_id
order by delivery_type, customer_id;

I have no idea why you would attempt a loop to do this. You should just use set-based logic in SQL.

EDIT:

Based on your comment, use window functions:

select d.*
from (select delivery_type, customer_id,
             count(*) as cnt,
             row_number() over (partition by delivery_type order by count(*) desc) as seqnum
      from deliveries
      group by delivery_type, customer_id
     ) d
where seqnum <= 10
order by delivery_type, customer_id;