Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

CONTINUE OF CORRECT ANSWER IN ID:39913692

SELECT category cat1,
       cat cat2,
       COUNT(*) quantity,
       COUNT(CASE WHEN TYPE = 'P' THEN 1 END) type_p,
       COUNT(CASE WHEN TYPE = 'T' THEN 1 END) type_t
  FROM yourtable
GROUP BY category, cat
ORDER BY cat1, cat2 DESC;

Open in new window


Hi,

This works perfect for the answer work in that topic, what about if i want to create a column to count the respective fields that contain any of these words: rec, ric or roc. This is similar to the count(case... but i want to do something similar with a like for any of these three words, any idea?
0
joyacv2
Asked:
joyacv2
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Not sure I follow but to use the same type of count with three words:

COUNT(CASE WHEN TYPE in ('rec','ric','roc') then 1 end)
0
 
joyacv2Author Commented:
Hi,

no work because the rec, ric, roc is a part of the string, not the complete string, this is something that i forget to mention
0
 
slightwv (䄆 Netminder) Commented:
>>this is something that i forget to mention

We cannot read minds...  ;)

then multiple 'or's?

COUNT(CASE WHEN TYPE like '%rec%' or TYPE like  '%ric%' or TYPE like  '%roc%') then 1 end)

move the wildcards however you need them.
0
 
joyacv2Author Commented:
yes, you cannot read minds, but can solve problems, jajaja, perfect solution!!! Thanks!!!!
0
 
Ray PaseurCommented:
You can use the percent sign for a wild card.

If you want to post the CREATE TABLE statement and a segment of code and data that will load the table, you will be able to get much faster answers, probably including complete code examples.  Just a thought...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now