CODEWITHSUNDEEP
sqlsql-servert-sql
user10537311
user10537311

Reputation:

Count percent of all peaple who got specific email

I need to count and make percent of people who use specific email. Like from all people use @gmail.com, 55% people. I got just one table, in what table date is like [email protected] or [email protected]

I can't found solution to make it. I try like operator in Count function, but don't work it for me... got the error:

Incorrect syntax near ')'

Here is my query: 

SELECT  email, count(email Like '%[email protected]') * 100.0 / sum(count(*)) over(), count(email Like 
 '%[email protected]') * 100.0 / sum(count(*)) over()
FROM people 
GROUP BY email
ORDER BY COUNT(*) DESC

I need to get something like what https://www.part.lt/img/866951b6da637a118364f85d1baee3b2526.png

Upvotes: 0

Views: 76

Answers (5)

beyt3i
beyt3i

Reputation: 31

with the following code, you don't need to add email domain manually to your script. It will fetch automatically and save your time:

create table #tmp (email varchar(100))

insert into #tmp 
(email)
values
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]');

declare @total int
select @total=count(1) from #tmp

select
SUBSTRING(email,PATINDEX('%@%',email),LEN(email)) emailDomain
,Count(SUBSTRING(email,PATINDEX('%@%',email),LEN(email))) numberofuser
,Count(SUBSTRING(email,PATINDEX('%@%',email),LEN(email)))/(1.*@total) percentage
from #tmp
group by SUBSTRING(email,PATINDEX('%@%',email),LEN(email))

Upvotes: 0

Eray Balkanli
Eray Balkanli

Reputation: 7960

You can benefit from cte structure like below as an alternative solution:

;with cte as (
  SELECT  sum(case when email Like '%@gmail.com' then 1 else 0 end) as GmailUsers,
          sum(case when email Like '%@yahoo.com' then 1 else 0 end) as YahooUsers,
          count(*) AllUsers
  FROM people 
)
select GmailUsers,YahooUsers, 
       (cast(GmailUsers as float)/cast(AllUsers as float))*100.0 as GmailUsersPercentage,
       (cast(YahooUsers as float)/cast(AllUsers as float))*100.0 as YahooUsersPercentage
from cte

Upvotes: 1

Andrei
Andrei

Reputation: 1216

wouldn't it just be easy enough to do 3 queries select count(*) from email where email like '%[email protected]%

declare @total int = select count(*) from people
SELECT (count(*) / @total) * 100 FROM people where email like '%[email protected]'
SELECT (count(*) / @total) * 100 FROM people where email like '%[email protected]'
SELECT (count(*) / @total) * 100 FROM people where email like '%[email protected]'

Upvotes: 0

Hogan
Hogan

Reputation: 70513

SELECT 
 (SUM(CASE WHEN email Like '%[email protected]' THEN 1 ELSE 0 END) / count(*)) * 100
FROM people

Upvotes: 0

Thom A
Thom A

Reputation: 95554

count(email Like '%[email protected]') doesn't make any sense, it's just a wayawrd boolean expression.

I suspect what you want is

 SELECT email,
        COUNT(CASE WHEN email Like '%[email protected]' THEN 1 END) * 100.0 / count(email) over(), --Count perform a COUNT in a SUM
        COUNT(CASE WHEN email Like '%[email protected]' THEN 1 END) * 100.0 / COUNT(email) over()
 FROM people 
 GROUP BY email;

I can't, however, test to ensure this works.

Upvotes: 0

Related Questions