Print all the row with two or more element in the column



  • In following table how to print the student number when age occur two or more times, The result should be only column Sno i.e.
    Result

    Sno Reason
    1 because 18 occur two times
    2 because 20 occur three times
    4 because 20 occur three times
    6 because 20 occur two times
    8 because 18 occur two times
    10 because 18 occur two times

    Student Table

    Sno Sname Smarks Sage
    1 A 90 18
    2 B 94 20
    3 C 87 21
    4 D 95 20
    5 E 97 19
    6 F 80 20
    7 G 85 22
    8 H 96 17
    9 I 84 20
    10 J 93 18


  • Given the following set of data:

    enter image description here

    First, we want to find out how many times a given age occurred for each row.

    Using an aggregate function with GROUP BY will result in reduction of rows, which is not what we want.

    SELECT age, COUNT(id)
    FROM students
    GROUP BY age
    ORDER BY age ASC
    

    enter image description here We don't want this.

    Instead, we could use window functions to count how many times a given age occurred for each row. We can use window functions for that. Window functions operates on a set of rows, without reducing the number of rows. The condition is specified by the "PARTITION BY" keyword.

    SELECT id, age, COUNT(id) OVER (PARTITION BY age) AS occurrence
    FROM students
    

    enter image description here We want this.

    https://www.mysqltutorial.org/mysql-window-functions/

    Now, according to the requirements, we only want to select the records where age occurred 2 times or more (> 1). So simply wrapping it up in a CTE and filter out the records where the occurrence is > 1 will give you the final results.

    https://www.mysqltutorial.org/mysql-cte/

    WITH duplicate_age_cte AS (
        SELECT id, age, COUNT(id) OVER (PARTITION BY age) AS occurrence
        FROM students
    )
    SELECT cte.id, cte.age, ('because ' || cte.age || ' occurs ' || cte.occurrence || ' times') AS reason
    FROM duplicate_age_cte cte
    WHERE cte.occurrence > 1
    ORDER BY cte.id ASC
    

    Results

    enter image description here

    ('because ' || cte.age || ' occurs ' || cte.occurrence || ' times') AS reason
    

    This is just to display the column in a human readable format, according to the requirements.

    The steps above are my thought process leading to the final results.

    I apologize for bad English, not a native speaker.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2