Writing the filtering query to ignore some strings

Question

Hi im obtaining data from a table through a query.. i need to omit some of the data types, currently i cant write the query for that

+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT001 | William Norton       | UK        | Cambridge      |
| AUT002 | William Maugham      | Canada    | Toronto        |
| AUT003 | William Anthony      | UK        | Leeds          |
| AUT004 | S.B.Swaminathan      | India     | Bangalore      |
| AUT005 | Thomas Morgan        | Germany   | Arnsberg       |
| AUT006 | Thomas Merton        | USA       | New York       |
| AUT007 | Piers Gibson         | UK        | London         |
| AUT008 | Nikolai Dewey        | USA       | Atlanta        |
| AUT009 | Marquis de Ellis     | Brazil    | Rio De Janerio |
| AUT010 | Joseph Milton        | USA       | Houston        |
| AUT011 | John Betjeman Hunter | Australia | Sydney         |
| AUT012 | Evan Hayek           | chillie   | Chillie        |
| AUT013 | E. Howard            | Spain     | Madrid         |
| AUT014 | C. J. Wilde          | UK        | London         |
| AUT015 | Butler Andre         | Austria   | Florida        |
+--------+----------------------+-----------+----------------+

i want to omit brazil, australia and chillie.. i tried query similer to below but it dint work

SELECT d.country, COUNT(*) AS number FROM ..............
 AND d.country NOT LIKE 'brazil' OR 'Austria' OR 'Chillie'
 GROUP BY d.country

but it works for one country like this

SELECT d.country, COUNT(*) AS number FROM ..............
 AND d.country NOT LIKE 'brazil'
 GROUP BY d.country

can anybody help me to do the filtering and get rid of the terms i want

Thanks for all the useful replies guys!!!

Prog_Rookie
– Prog_Rookie

2015-09-14 11:06:38 +00:00
Commented Sep 14, 2015 at 11:06 — Prog_Rookie
– Prog_Rookie, Commented Sep 14, 2015 at 11:06

jarlh · Accepted Answer · 2015-09-14 10:47:24Z

1

Use NOT IN:

SELECT d.country, COUNT(*) AS number FROM ..............
 AND d.country NOT IN ('brazil', 'Austria', 'Chillie')
 GROUP BY d.country

answered Sep 14, 2015 at 10:47

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shilpa Soni · Accepted Answer · 2015-09-14 10:47:43Z

1

NOT IN will work for you:

SELECT d.country, COUNT(*) AS number FROM tbl
 where d.country NOT in ('brazil','Austria','Chillie')
 GROUP BY d.country

answered Sep 14, 2015 at 10:47

Shilpa Soni

2,1724 gold badges28 silver badges38 bronze badges

Comments

DevT · Accepted Answer · 2015-09-14 10:49:15Z

1

SELECT d.country, COUNT(*) AS number FROM ..............
AND d.country NOT IN ('brazil', 'Austria', 'Chillie')
GROUP BY d.country

answered Sep 14, 2015 at 10:49

DevT

4,95316 gold badges63 silver badges95 bronze badges

Comments

Brave Soul · Accepted Answer · 2015-09-14 10:49:38Z

1

You can achive using AND

 SELECT d.country, COUNT(*) AS number FROM ..............
 AND d.country <> 'brazil' AND d.country <>'Austria' AND d.country <>'Chillie'
 GROUP BY d.country

OR NOT IN

 SELECT d.country, COUNT(*) AS number FROM ..............
 AND d.country NOT IN ('brazil','Austria','Chillie')
 GROUP BY d.country

answered Sep 14, 2015 at 10:49

Brave Soul

3,6504 gold badges33 silver badges51 bronze badges

Comments

Brave Soul · Accepted Answer · 2015-09-16 07:38:44Z

1

Do this... [edit]

 SELECT d.country, COUNT(*) AS number FROM ..............
     AND d.country NOT LIKE 'brazil' AND d.country NOT LIKE 'Austria' AND d.country NOT LIKE 'Chillie'
     GROUP BY d.country;

Above works but other people answers are best...!

edited Sep 16, 2015 at 7:38

Brave Soul

3,6504 gold badges33 silver badges51 bronze badges

answered Sep 14, 2015 at 10:47

Chella

1,5624 gold badges23 silver badges43 bronze badges

1 Comment

jarlh Over a year ago

But this query is WRONG! It should be AND all the way, not OR d.country NOT LIKE 'Chillie'.

Collectives™ on Stack Overflow

Writing the filtering query to ignore some strings

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related