Oracle SQL: Count number of rows with specific value

Question

I have a table like this:

ID | AGE 
--------
 1 | 27
 2 | 24
 1 | 25
 4 | 25
 1 | 25
 5 | 25
 5 | 25
...|...

As you can see, ID and AGE are not unique (there is a second ID that is unique, but that's irrelevant here).

I want to return all the distinct IDs with more than one specific value: for example, counting all the IDs with more than one AGE = 25; in the table above, this should yield [1, 5], (ID = 4 is not counted because 25 occurs only once; ID = 1 and ID = 5 IS included because 25 occurs more than once in AGE).

Basically what I want in invalid SQL:

SELECT DISTINCT id FROM table WHERE count(AGE = 25) > 1

Unfortunately, count(AGE = 25) is invalid. How would I write this out in SQL?

You can simply do this in SQL

ajmalmhd04
– ajmalmhd04

2013-11-20 08:45:21 +00:00
Commented Nov 20, 2013 at 8:45 — ajmalmhd04
– ajmalmhd04, Commented Nov 20, 2013 at 8:45

Mithrandir · Accepted Answer · 2013-11-20 08:43:52Z

3

Do you want something like this?

SELECT 
 ID, COUNT(ID)
FROM table
WHERE Age = 25
GROUP BY ID
HAVING COUNT(ID) > 1

edited Nov 20, 2013 at 8:43

answered Nov 20, 2013 at 8:32

Mithrandir

25.5k6 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

George Newton Over a year ago

It it possible to count, foreach distinct ID, the number of rows where AGE = 25?

Mithrandir Over a year ago

I changed my answer to what, i think, you want.

Collectives™ on Stack Overflow

Oracle SQL: Count number of rows with specific value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related