select query using count

user_5571656 0
Viewed 75

enter image description here

i want to count number of employees who have gmail or yahoo account the result should be

email,         count
gmail.com      3
yahoo.com      2

tried so far

select count(emailid) 
from employee 
where emailid IN (select emailid from employee 
             WHERE emailid like '%@gmail.com' 
                  or select emailid from employee WHERE emailid like '%@yahoo.com')
count select mysql sql
3 Answers

try this

SELECT Substring (EmailId, Charindex( '@', EmailId ) + 1, Len(EmailId) )  , Count( EmpID)
FROM Emp
group by  Substring (EmailId, Charindex( '@', EmailId ) + 1, Len(EmailId) )

Use function Substring to Get the email suffix and then grouping by the email suffix

If you have emails other than gmail and yahoo and you only want the mentioned emails, use the where clause

SELECT Substring (EmailId, Charindex( '@', EmailId ) + 1, Len(EmailId) )  , Count( EmpID)
FROM Emp
WHERE Substring (EmailId, Charindex( '@', EmailId ) + 1, Len(EmailId)) IN ('gmail.com','yahoo.com')
group by  Substring (EmailId, Charindex( '@', EmailId ) + 1, Len(EmailId) )
user_39185420 
select SUBSTRING_INDEX(Email,'@',-1), count(*) Count
from employee 
where Email like '%gmail.com' or  email like '%yahoo.com'
group by  SUBSTRING_INDEX(Email,'@',-1)
user_9252220

Maybe an easier solution if you need to count only emails with Gmail and yahoo should be:

select count(*) as count,case when emailid like'%gmail%' then 'gmail.com' 
                              when emailid like'%yahoo%' then 'yahoo.com' end as email 
from employee
group by email;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/173

Or if you only have emails with yahoo or Gmail check:

select count(*) as count,case when emailid like'%gmail%' 
                              then 'gmail.com' 
                              else 'yahoo.com' end as email 
from employee
group by email;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/172

Result:

count email
3     gmail.com
2     yahoo.com
user_164619520
Related