CODEWITHSUNDEEP
mysqlsql
Aaron McKelvie
Aaron McKelvie

Reputation: 167

SQL query to effectively have a "WHERE CLAUSE" after GROUP BY

I was wondering if it is possible to select all unique rows (by doing a group by columnname) where another column doesn't contain specific values. EG:

users table
+-------+-----------+
| NAME  | COUNTRY   |
+-------+-----------+
| Bob   | Australia |
+-------+-----------+
| Bob   | America   |
+-------+-----------+
| Steve | Australia |
+-------+-----------+
| Sam   | Australia |
+-------+-----------+
| Sam   | America   |
+-------+-----------+

What I'm looking to do is get all rows grouped by NAME where COUNTRY doesn't contain America.

PSUEDO-SQL: SELECT * FROM users GROUP BY Name WHERE COUNTRY NOT LIKE 'America' (obviously this doesn't work but it's for clarification)

Output from above:

+-------+-----------+
| NAME  | COUNTRY   |
+-------+-----------+
| Steve | Australia |
+-------+-----------+

Can this be done or am I approaching this the wrong way?

EDIT: I am unsure of how to word title so if someone has a better suggestion please update it/let me know

Upvotes: 1

Views: 76

Answers (3)

Sam Ferguson
Sam Ferguson

Reputation: 21

SELECT name, country FROM users WHERE country <> 'America' GROUP BY country HAVING count(name) = 1

EDIT: This is my first ever Stack Overflow post so open to constructive feedback. Look forward to hearing your thoughts.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You should not use select * with group by. Perhaps you want:

SELECT name, country
FROM users 
WHERE COUNTRY NOT LIKE 'America'

Or if you want names that are not in 'America':

SELECT name
FROM users
GROUP BY name
HAVING SUM(COUNTRY LIKE 'America') = 0;

Upvotes: 2

kuriouscoder
kuriouscoder

Reputation: 5582

The following query should work. 

SELECT DISTINCT name, country FROM table WHERE country != 'America'

Upvotes: 1

Related Questions