Oracle SQL: How to write below SQL in Oracle

user_8230537 0
Viewed 90

There is a table t1:

id type 
1    a
1    b
2    c
2    a
3    a
3    a
4    a

Now I need to check if the id only has type a and the count is 1 (single), i.e., only id 4 satisfies this condition in the data above

SELECT type, COUNT (1)
FROM t1
where id = :id
GROUP BY type
HAVING COUNT (1) = 1;

I use the above SQL query to get the data and then use it in code. It's not a good solution, can anyone help me to get the correct result with one SQL query?

count select group-by oracle sql
4 Answers

I'd group by the ID and filter on two counts:

  1. Total count is 1
  2. Count of rows that aren't type a (using a case statement) is 0


SELECT   id
FROM     t1
GROUP BY id
HAVING   COUNT(*) = 1 AND COUNT(CASE WHEN type <> 'a' THEN 1 END) = 0
user_24227760

You want a simple aggredated query with a HAVING BY clause that ensures that only one row exists and that its type is equal to 'a'.

SELECT id
FROM t1
GROUP BY id
HAVING COUNT(*) = 1 and SUM(DECODE(type, 'a', 0, 1)) = 0
user_106767160

You need id in group by clause & just filter out the types with having clause :

SELECT id
FROM t1
GROUP BY id
HAVING MIN(type) = MAX(type) AND MIN(type) = 'a';
user_55294450

I would simply do:

SELECT id
FROM t1
GROUP BY id
HAVING COUNT(*) = 1 AND MIN(type) = 'a';
user_11440350
Related