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?
LVL 1
joyacv2Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
slightwv (䄆 Netminder)Connect With a Mentor 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
 
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
 
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
All Courses

From novice to tech pro — start learning today.