Dick Dick - 5 months ago 8
SQL Question

Distinct number of specific items in list

I rarely do stuff in MySQL, so for me this is rocket science ...

I want to know how many times distinct values starting with "abc-" are present in a list.

So for example how many times "abc-table" and "abc-sofa" are present.

The table:

| object
-----------
| abc-table
| def-table
| ghi-chair
| abc-sofa
| abc-table


The result should be like:

| name number
-------------------
| abc-table 2
| abc-sofa 1


(Excuse me for the badly formatted tables.)

I tried the following, but that turns out to be incorrect:

SELECT object, COUNT(DISTINCT object) WHERE object LIKE abc-% FROM table GROUP BY object


Any help is appreciated.

Answer
  • WHERE clause should be after FROM.
  • Use single quote ' for the LIKE operator.
  • No need of DISTINCT in your case.

Try the below query:

SELECT `object` AS `name`, COUNT(`object`) AS `number`
FROM table 
WHERE `object` LIKE 'abc-%' 
GROUP BY `object`
ORDER BY COUNT(`object`) DESC; -- add order by if you need to sort by count

Result:

name      number
----------------
abc-table   2
abc-sofa    1

DEMO