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.

Waldgrave

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

Remorse

(1) after Count means which parameter after SELECT shall be count? – Waldgrave

No, the (1) is not a reference to any column, it's just the integer value 1. It's more typical to use COUNT(*) or COUNT(columnname). Glomek is using COUNT(1) as a constant integer value, because some people believe this is faster than referencing a column. – Eggshell

The optimizer should take care of making count(*) take just as much time as count(1). I guess some optimizers don't. – Pillage

Thx so much guys, helped a lot! God bless all of you who help – Waldgrave

...and you can add `order by count(1);' to.. err.. order by count :P – Inconsumable