SQL query to count number of different values

Question

x y

A P
A P
B P
B Q

Hi, I need a query to return for all unique values of x, how many different y's. So for the above data it would return:

x count
A 1
B 2

Thanks

Answer 1

Use GROUP BY and COUNT(DISTINCT ...):

SELECT x, COUNT(DISTINCT y) AS cnt_y
FROM yourtable
GROUP BY x

Result:

x    cnt_y
A    1
B    2

Test data:

CREATE TABLE yourtable (x VARCHAR(100) NOT NULL, y VARCHAR(100) NOT NULL);
INSERT INTO yourtable (x, y) VALUES
('A', 'P'),
('A', 'P'),
('B', 'P'),
('B', 'Q');

Answer 2

This is the simple case for a GROUP BY statement.

Here's some code:

SELECT x, COUNT(DISTINCT y) AS y
FROM table
GROUP BY x;