CODEWITHSUNDEEP
sqlpostgresql
d.tang
d.tang

Reputation: 37

Finding percent of total within a column

I have the following query that gives the split of our users based on if they came in from web or mobile. Instead of just the count of users, I want to show the percent of total users for each source. How would I calculate this in one query? 

select source, count(*) as total_users
from table
where is_active = 1
and source in ('web','mobile')
group by 1

Upvotes: 0

Views: 46

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269833

I would just write this using window functions:

select source, count(*) as total_users,
       count(*) / sum(count(*)) over () as ratio
from table
where is_active = 1 and
      source in ('web', 'mobile')
group by source;

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

An alternative presentation:

SELECT
  100.0*count(case when source = 'mobile' then 1 end) as mcount/count(*),
  100.0*count(case when source = 'web' then 1 end) as wcount/count(*)
FROM
  t
WHERE 
  source in ('web','mobile') and is_active = 1

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

try like below

 with cte as
   ( select source, count(*) as total_users     
    from table
    where is_active = 1
    and source in ('web','mobile')
    group by source
  ) SELECT source,total_users,  
   total_users * 100.0 / SUM(total_users) OVER () AS percentage from cte

Upvotes: 0

MatBailie
MatBailie

Reputation: 86715

SELECT
  *,
  total_users * 100.0 / SUM(total_users) OVER () AS percentage_of_total
FROM
(
    select source, count(*) as total_users
    from table
    where is_active = 1
    and source in ('web','mobile')
    group by source
)
  totals_by_source

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6c0af52dcb10b072b876ae593773e148

Upvotes: 2

Related Questions