I have table called [machine] with columns id and mach_model. If I query
...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:
I want distinct and count for the same table simultaneously.
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:
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:
answered Dec 24, 2015 at 12:12 PM
Tom Staab ♦