CODEWITHSUNDEEP
sqlpostgresql
CodeL
CodeL

Reputation: 191

Postgres get rows having at least 1 specific value in column group by another column

I have an sql command:

select location,status,total from my_table

This return:

location - status - total
city a - active - 3
city a - pending - 2
city a - cancel - 4
city b - pending - 4
city b - cancel - 4
city c - active - 2
city c - cancel - 6

How can i return only the rows with cities where having at least once the status active and total >= 1?

So, i want to return:

    location - status - total
    city a - active - 3
    city a - pending - 2
    city a - cancel - 4
    city c - active - 2
    city c - cancel - 6

As you notice i dont want to return the city b cause it hasn't the status active at least one time with total >= 1.

Upvotes: 3

Views: 1460

Answers (1)

Georgi Raychev
Georgi Raychev

Reputation: 1334

select location,status,total from my_table
where location in (
    select location from my_table
    where status = 'active'
        and total >= 1
    )

Upvotes: 1

Related Questions