CODEWITHSUNDEEP
sqlsql-servert-sql
RAP
RAP

Reputation: 83

Want to count Distinct values in column 1 based on column 2 values

Here is my sample data for the select statement

banner_id   subject_code

N00012301   MATH

N00012963   ENGL

N00012963   MATH

N00013406   ENGL

N00013406   ENGL

N00013406   MATH

N00013998   ENGL

N00016217   MATH

N00017367   MATH

N00017367   ENGL

N00017833   MATH

N00018132   MATH

N00019251   ENGL

N00019251   ENGL

N00019312   MATH

N00019312   ENGL


N00019312   ENGL

N00020261   ENGL

i want count of banner_id where it has both 'engl' and 'math' ex:

N00019312   MATH

N00019312   ENGL

N00019312   ENGL

is 1 value based on banner_id though it has 2 engl and 1 math

Appreciate your help,

thanks

Upvotes: 1

Views: 95

Answers (3)

paparazzo
paparazzo

Reputation: 45096

select count(*)
from
(
  SELECT banner_id
  FROM Table
  WHERE subject_code = 'engl'
INTERSECT
  SELECT banner_id
  FROM Table
  WHERE subject_code = 'math'
)  tt

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Try this:

SELECT banner_id AS [COUNT]
FROM @t
WHERE subject_code IN ('ENGL', 'MATH')
GROUP BY banner_id
HAVING COUNT(DISTINCT subject_code) > 1

If you want to just count then:

SELECT  COUNT(*)
FROM    ( SELECT    banner_id AS [COUNT]
          FROM      @t
          WHERE     subject_code IN ( 'ENGL', 'MATH' )
          GROUP BY  banner_id
          HAVING    COUNT(DISTINCT subject_code) > 1
        ) t

Upvotes: 1

Patrick Tucci
Patrick Tucci

Reputation: 1952

If you want to see the banner_ids, this should get you each banner_id where the banner_id exists with both math and engl:

SELECT banner_id
FROM YourTable
WHERE subject_code IN ('engl', 'math')
GROUP BY banner_id
HAVING COUNT(DISTINCT subject_code) > 1

Upvotes: 3

Related Questions