select query using count-CodePudding

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')

CodePudding user response：

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) )

CodePudding user response：

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)

CodePudding user response：

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