How to use DISTINCT command in SQL

Question

Below shows my data table name called "company"

enter image description here

Here is my SQL query

SELECT 
    name, COUNT(DISTINCT num) AS count
FROM 
    company
WHERE 
    (num LIKE '51%' OR num LIKE '65%' OR num LIKE '81%')
GROUP BY 
    name

After run this query it shows below result

enter image description here

But I need below result. Please help me to solve my problem. Thank you

enter image description here

Why are you using the like operator for the num column, is it a varchar? — Tom
– Tom, Commented May 11, 2015 at 8:15
So you want to count the distincts numbers with only 2 first digits ? — Seblor
– Seblor, Commented May 11, 2015 at 8:21

Tom · Accepted Answer · 2015-05-11 08:24:30Z

4

I assume you want to have the distinct on the first 2 characters of the column.

You can use the function LEFT() for this:

Query

SELECT name , COUNT(DISTINCT LEFT(num, 2)) AS count
FROM new_table
WHERE ( num LIKE '51%' OR num LIKE '65%' OR num LIKE '81%')
GROUP BY name

answered May 11, 2015 at 8:24

Tom

3322 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1