Sum different items in two different columns

I’d like to have two columns that show the sum of different items purchased. For example

DB Name: Purchases

CustomerID	Shirt
123	Red
123	Red
123	Red
123	Blue
123	Blue

The result I’d like to get is the following

CustomerID	Red	Blue
123	3	2

I am able to create one column but I am having difficulty creating two different columns.

Thank you.

Answer

Just use conditional aggregation. This is particularly simple in MySQL:

select customerid,
       sum(shirt = 'Red') as red,
       sum(shirt = 'Blue') as blue
from t
group by customerid;

Here is a db<>fiddle.

The above uses a MySQL shortcut. The general syntax uses case:

select customerid,
       sum(case when shirt = 'Red' then 1 else 0 end) as red,
       sum(case when shirt = 'Blue' then 1 else 0 end) as blue
from t
group by customerid;