CODEWITHSUNDEEP
sql
Robin
Robin

Reputation: 3

How to group rows in SQL with a special condition?

I have a table like this in my database:

+-----------+-------+--------+--------------+
| Person_ID | Month |  Name  |   Special    |
+-----------+-------+--------+--------------+
|       807 |    01 | Smith  | othercontent |
|       807 |    01 | Smith  | othercontent |
|       807 |    02 | Smith  | othercontent |
|       807 |    02 | Smith  | othercontent |
|       807 |    02 | Smith  | content      |
|       807 |    02 | Smith  | othercontent |
|       807 |    03 | Smith  | othercontent |
|       807 |    03 | Smith  | othercontent |
|       807 |    03 | Smith  | othercontent |
|       807 |    04 | Smith  | othercontent |
|       807 |    04 | Smith  | content      |
|       807 |    04 | Smith  | othercontent |
|       981 |    01 | Walker | othercontent |
|       981 |    01 | Walker | othercontent |
|       981 |    02 | Walker | othercontent |
|       981 |    02 | Walker | othercontent |
|       981 |    02 | Walker | content      |
|       981 |    02 | Walker | othercontent |
|       981 |    03 | Walker | othercontent |
|       981 |    03 | Walker | othercontent |
|       981 |    03 | Walker | othercontent |
|       981 |    04 | Walker | othercontent |
|       981 |    04 | Walker | content      |
|       981 |    04 | Walker | othercontent |
+-----------+-------+--------+--------------+

I have to write a SQL-Query that selects every month that does not contain "content" at least once. And it should only give out one line.

So the output should be like this:

+-----------+-------+--------+--------------+
| Person_ID | Month |  Name  |   Special    |
+-----------+-------+--------+--------------+
|       807 |    01 | Smith  | othercontent |
|       807 |    03 | Smith  | othercontent |
|       981 |    01 | Walker | othercontent |
|       981 |    03 | Walker | othercontent |
+-----------+-------+--------+--------------+

How can I accomplish that with a SQL-Query? I tried to do that with the group by function, but could not get it working properly.

Upvotes: 0

Views: 90

Answers (3)

Rajat
Rajat

Reputation: 5803

I think you just need

select distinct *
from t t1
where not exists(select 1 from t t2 where t1.month=t2.month and t2.special = 'content')

Upvotes: 0

Sahar Rezazadeh
Sahar Rezazadeh

Reputation: 333

use this one

select *
from t
where month not in ( select month from t as t2 where t2.special = 'content')
group by person_id, month, name, special

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want only one row per person/month combination, then use aggregation. You have use a having clause to filter out the 'content' rows:

select person_id, month, name, max(special)
from t
group by person_id, month, name
having sum(case when special = 'content' then 1 else 0 end) = 0;

Upvotes: 1

Related Questions