CODEWITHSUNDEEP
mysqlsqlselect
Funkytown
Funkytown

Reputation: 560

MYSQL Count the Number of Colors of Each Type of Item

Imagine I've got a table that records transactions in a clothing store. Each item comes in different variety (like different colors). I want to write a query that will return a count of the number of each item sold, but ALSO tell me how many different colors have been sold of each item.

So if I've got a table like this:

+------------+
|transactions|
+------+-------+
| item | color |
+------+-------+
|shirt | red   |
|shirt | red   |
|shirt | blue  |
|shirt | green |
|hat   | blue  |
|hat   | red   |
|pants | blue  |
|pants | blue  |
|pants | blue  |
+------+-------+

I would want a result that looked something like this:

+------+-------+--------+
| item | count | colors |
+------+-------+--------+
|shirt | 4     | 3      |
|hat   | 2     | 2      |
|pants | 3     | 1      |
+------+-------+--------+

Getting the count of each item sold is easy enough. I know I can use a query like:

SELECT item, COUNT(*) as count FROM transactions GROUP BY item

The part I'm not sure about is how to also get a count of the number of different colors of each item.

Upvotes: 1

Views: 2386

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You are looking for count(distinct):

SELECT item, COUNT(*) as num, COUNT(DISTINCT color) as num_colors
FROM transactions
GROUP BY item;

Upvotes: 6

Related Questions