Counting the instances of customers

Question

Say that I have a table with one column named CustomerId. The example of the instance of this table is :

CustomerId
14
12
11
204
14
204

I want to write a query that counts the number of occurences of customer IDs. At the end, I would like to have a result like this :

CustomerId      NumberOfOccurences
14              2
12              1
11              1
204             2
14              1

I cannot think of a way to do this.

Answer 1

This is the most basic example of GROUP BY

SELECT CustomerId, count(*) as NumberOfOccurences
FROM tablex GROUP BY CustomerId

Answer 2

Practice exercise #3 on this page explains how to do this.

CREATE TABLE customers
(   customer_id     number(10)  not null,
    customer_name   varchar2(50)    not null,
    city    varchar2(50),   
    CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);          

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7001, 'Microsoft', 'New York');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7002, 'IBM', 'Chicago');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7003, 'Red Hat', 'Detroit');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7004, 'Red Hat', 'New York');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7005, 'Red Hat', 'San Francisco');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7006, 'NVIDIA', 'New York');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7007, 'NVIDIA', 'LA');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7008, 'NVIDIA', 'LA');

Solution:

The following SQL statement would return the number of distinct cities for each customer_name in the customers table:

SELECT customer_name, COUNT(DISTINCT city) as "Distinct Cities"
FROM customers
GROUP BY customer_name;

It would return the following result set:

CUSTOMER_NAME   Distinct Cities
IBM     1
Microsoft   1
NVIDIA  2
Red Hat     3