Find records only all value are same in column otherwise return 0-CodePudding

I have the following table

id	name
1	Gaurav
1	Ram
1	Gaurav
1	Gaurav

From the above table I want to fetch records if name have same value as Gaurav. For example one row has name Ram so it should not return any thing. If all value is Gaurav then return id.

CodePudding user response：

On MySQL, you could use aggregation:

SELECT id
FROM yourTable
GROUP BY id
HAVING SUM(name = 'Gaurav') = COUNT(*);

On all databases:

SELECT id
FROM yourTable
GROUP BY id
HAVING COUNT(CASE WHEN name = 'Gaurav' THEN 1 END) = COUNT(*);

CodePudding user response：

You can try this as well. Bit hardcoded. You can use 0 instead of null and remove where clause as well if you want.

SELECT Case When Name ='Gaurav' Then ID else NULL END  AS ID 
FROM Yourtable
where name ='Gaurav'

CodePudding user response：

You can use having clause

select `id` from `tablname` group by `name` having count(`name`)>1