Dick Dick - 1 year ago 65
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 Source
  • 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


name      number
abc-table   2
abc-sofa    1


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download