How to give " Only in " condition in SQL?-CodePudding

I have a table that has columns Name, Series and Season.

Name	series	season
abc	alpha	s1
abc	alpha	s2
pqr	alpha	s1
xyz	beta	s2
xyz	gamma	s3
abc	theta	s1

I am trying to extract the number of people who have watched only the series 'alpha', and not any other series. How to get this count?

On giving the "where series='alpha' " condition, I get the counts of people who watched alpha, but not the counts of those who watched only alpha eg: abc has watched alpha as well as theta, but pqr has watched only alpha.

CodePudding user response：

If you really want to get the number of such people only, without their name or any further information, you can use a NOT EXISTS clause like this:

SELECT COUNT(*) AS desiredColumnName
FROM yourtable y
WHERE series = 'alpha'
AND NOT EXISTS 
(SELECT 1 FROM yourtable y1 WHERE y.name = y1.name AND series <> 'alpha');

Thus, you can set your condition the person must not appear in any other series but the 'alpha' one.

If the same name can occur in different seasons, you can add a DISTINCT to count them only once. This should only be done if really required because it can slow down the query:

SELECT COUNT(DISTINCT name) AS desiredColumnName
FROM yourtable y
WHERE series = 'alpha'
AND NOT EXISTS 
(SELECT 1 FROM yourtable y1 WHERE y.name = y1.name AND series <> 'alpha');

If your description is incorrect and you need also other information, you might do better with a GROUP BY clause etc.

Try out here: db<>fiddle

CodePudding user response：

You can use subquery, like this:

SELECT COUNT(DISTINCT name)
FROM t 
WHERE name NOT IN (
    SELECT DISTINCT name
    FROM t
    WHERE series<>'alpha'
) AND series='alpha'

CodePudding user response：

You could use a subquery to get only the names which have watched only distinct series and then filter in the where condition your specific serie

select count(yt.name) as  only_alpha
from yourtable yt
inner join ( select name 
             from yourtable
             group by name
             having count(distinct series) = 1
           ) yt1 on yt.name=yt1.name
where yt.series='alpha';

https://dbfiddle.uk/n0PavP4H

CodePudding user response：

You can use like below

select sum(Record) as Count from (select count() as Record from yourtable where series='alpha' group by series,name having count()=1) as data

Check below link

https://dbfiddle.uk/1Y3WZT23

CodePudding user response：

I added some new cases to your table to see other anomalies that can happen. This is how it looks like now:

Name	series	season
abc	alpha	s1
abc	alpha	s2
abc	theta	s1
fgh	alpha	s1
fgh	alpha	s2
klj	gamma	s1
klj	gamma	s2
klj	gamma	s3
pqr	alpha	s1
xyz	beta	s2
xyz	gamma	s3

I maybe overcomplicated, but it can provide the correct result for your problem; you just need to COUNT() it. I tested other SQL queries under your question on my this table, but not all of them showed the correct figure. I doesn't recommend to use NOT IN ( sub query ) for the following reasons:

Please find my code here:

WITH helper_table as
(
    SELECT      "Name",
                series,
                count(1) as seasons_watched
    FROM        your_table
    GROUP BY    1, 2
)
------------------------------------------------------------
SELECT      t1."Name"
FROM
(
        SELECT      "Name",
                    count(1) as is_watched_at_least_one_series_of_alpha
        FROM        helper_table
        WHERE       series = 'alpha'
        GROUP BY    1
) t1

INNER JOIN
(
        SELECT      "Name",
                    count(1) as watched_exactly_one_series_so_far
        FROM        helper_table
        GROUP BY    1
        HAVING      count(1) = 1
) t2
ON          t2."Name" = t1."Name"
;

Hope it helps!