Distinct and count for same table in a single query.

I have table called [machine] with columns id and mach_model. If I query

 SELECT count(mach_model) FROM machine WHERE mach_model like '%etp%'


...the rows affected are 200. For the same query, if I filter with '%ttp%', the rows effected are 400. I need to write a query to get the following output:

 mach_type | count
 etp       | 200
 ttp       | 300

I want distinct and count for the same table simultaneously.


more ▼

asked Dec 24, 2015 at 06:01 AM in Default

avatar image

10 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

When I first read your title, I was expecting the answer to be to use GROUP BY, but that won't work with the wildcard values you specified. If you want exactly what you specified, this should work:

     mach_type = 'etp'
   , [count] = COUNT(*)
 FROM machine m
 WHERE m.mach_model LIKE '%etp%'
     mach_type = 'ttp'
   , [count] = COUNT(*)
 FROM machine m
 WHERE m.mach_model LIKE '%ttp%'

Not knowing your data, I have a few concerns. First, I don't know if "etp" and "ttp" can occur in the same mach_type value. The above method would count a row twice if it did contain both codes. Second, whenever I see wildcard searches like that I wonder if it could find false positives. Without delimiters around the "etp" or "ttp", what words might be accidentally found? You can find "bulletproof" and other matches here.

Is there a better way to group by machine type? Is the code in the same position (i.e. characters 10-12) all of the time? I'm wondering if perhaps a computed column for the code would make things easier. If you could, for example, have mach_type_code be some computed column of just the code on which you want to count rows for each distinct value, you could do this:

 SELECT mach_type_code, COUNT(*)
 FROM machine
 GROUP BY mach_type_code
 ORDER BY mach_type_code

more ▼

answered Dec 24, 2015 at 12:12 PM

avatar image

Tom Staab ♦
14.5k 7 14 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 24, 2015 at 06:01 AM

Seen: 60 times

Last Updated: Jan 02, 2016 at 01:45 AM

Copyright 2017 Redgate Software. Privacy Policy