How to count unique records and get number of these uniques in table using SQL?

Question

Imagine I have table like this:

id:Product:shop_id

1:Basketball:41

2:Football:41

3:Rocket:45

4:Car:86

5:Plane:86

Now, this is an example of large internet mall, where there are shops which sell to one customer, so customer can choose more products from each shop and buy it in one basket.

However, I am not sure if there is any SQL syntax which allows me to simply get unique shop_ids and total number of those shops' products in customer basket. So I'd get something like:

Shop 41 has 2 products

Shop 45 one product

Shop 86 two product

I can make SQL queries to scoop through table to make some kind of ['shop_id']['number_of_products'] array variable that would store all products' shop_ids, then "unique them" - up and count how many times I had to cut one more shop_id out to have some remaining but that just seems as a lot of useless scripting.

If you got some nice and neat idea, please, let me know.

Answer 1

This is exactly the sort of thing that aggregate functions are for. You make one row of output for each group of rows in the table. Group them by shop_id and count how many rows are in each group.

select shop_id, count(1) from TABLE_NAME
  group by shop_id