Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • 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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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