-2

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
2
  • 1
    What database system and version are you using,?...as that affects what answers are applicable, so please tag them.
    – J.D.
    Commented Mar 9, 2022 at 12:42
  • 1
    What have you tried thus far? Commented Mar 9, 2022 at 13:45

1 Answer 1

-1

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.

Read about window functions here

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.

Read about common table expression here

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.

2
  • Would you mind adding some context around what your query is doing and why you believe this is the correct answer? Commented Mar 9, 2022 at 19:18
  • Updated. Thank you. @BrendanMcCaffrey
    – TK Cheah
    Commented Mar 11, 2022 at 3:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.