CODEWITHSUNDEEP
sql
JasonGenX
JasonGenX

Reputation: 5444

how to SQL query with conditioned distinct

Simple Database:

street     | age
1st st     | 2
2nd st     | 3
3rd st     | 4
3rd st     | 2

I'd like to build a query that'll return the DISTINCT street names, but only for those households where no one is over 3.

so that result would be:

 street        | age
    1st st     | 2
    2nd st     | 3

How do I do that? I know of DISTINCT, but now how to conditionalize it for all the records that match the DISTINCT

Upvotes: 0

Views: 54

Answers (4)

Kashif Qureshi
Kashif Qureshi

Reputation: 1490

USE GROUP BY 

Select Street
from yourtable
group by street
Having sum(age)<=3

Another way this could be achived with a use of NOT EXISTS

SELECT *
FROM yourtable a
WHERE NOT EXISTS
    (SELECT street
     FROM yourtable b
     WHERE age > 3
       AND a.street = b.street)

Upvotes: 0

Little Santi
Little Santi

Reputation: 8803

SELECT DISTINCT street 
FROM table 
WHERE NOT(age>3)

Upvotes: 0

ltt
ltt

Reputation: 427

Suppose the name of the table is 'tab'. You can then try:

select distinct street from tab where street not in (select street from tab where age>3);

I have created a sql fiddle where you can view the result: http://sqlfiddle.com/#!9/2c513d/2

Upvotes: 1

Dan D
Dan D

Reputation: 2523

Distinct street names for households where no one is over 3:

SELECT street
FROM table
GROUP BY street
HAVING COUNT(1) <= 3

Upvotes: 0

Related Questions