Question 112858 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to give " Only in " condition in SQL?

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.

Tags:

5

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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'
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
  4. 0

    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

    Login or Signup to reply.
  5. 0

    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:

    1. Strange results when running SQL IN and NOT IN using Redshift
    2. Optimization for Large IN Lists
    3. Consider using EXISTS instead of IN with a subquery

    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!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search