CODEWITHSUNDEEP
mysqlsql
shrikanth
shrikanth

Reputation: 179

SQL with group by and having clause

I have a table A

ID   SALN  INDICATOR
  1    100    A
  2    100    B
  3    200    A
  4    200    C
  5    100    c
  6    300    B
  7    200    D

I need to pull SALN from table A after grouping by SALN and that group having indicator A,B ,C only

SALN
100

Please let me know the SQL for this

Upvotes: 0

Views: 82

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you want the three values, then you can do:

select saln
from A
where indicator in ('A', 'B', 'C')
group by saln
having count(*) = 3;

If there can be duplicates, then use:

having count(distinct indicator) = 3

If you want A, B, and C and no other values, then one way is:

select saln
from A
group by saln
having count(distinct case when indicator in ('A', 'B', 'C') then indicator end) = 3;

Upvotes: 0

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

select saln
from A
where indicator in('A','B','C')
group by saln
having sum(indicator) = 100

Upvotes: 1

juergen d
juergen d

Reputation: 204904

select saln
from your_table
group by saln
having sum(indicator not in ('A','B','C')) = 0

If you additionally need to check if A, B and C are present for a SALN then add

and sum(indicator  = 'A') > 0
and sum(indicator  = 'B') > 0
and sum(indicator  = 'C') > 0

to the end of the query.

Upvotes: 0

Related Questions