question

rajseo avatar image
rajseo asked

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. Thanks
countdistinct
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered
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: SELECT mach_type = 'etp' , [count] = COUNT(*) FROM machine m WHERE m.mach_model LIKE '%etp%' UNION ALL SELECT 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 "bull etproof" and other matches [here][1]. 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 ; [1]: https://www.morewords.com/contains/etp/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.