Início / dba / Perguntas / 331008
Accepted
tkmagnet
Asked: 2023-09-08 04:46:06 +0800 CST

Encontre a porcentagem com base nos valores de cada ID

  • 772

Tenho tabela1 com 2 IDs e 2 valores por ID (Y,N). Posso contar os valores pela seguinte consulta:

select id
 ,count(*) as "total"
 ,choice
from table1
where id in (1,8)
group by id, choice

Eu obtenho os seguintes resultados:

eu ia total escolha
1 55 N
1 17 S
8 319 N
8 123 S

Existe uma maneira de escrever uma consulta que me forneça as porcentagens de cada valor (Y,N) para cada ID?
(id1: 55/55+17 (N), 17/55+17 (Y), etc.)

Resultado desejado:

eu ia total escolha por cento
1 55 N 0,236
1 17 S 0,764
8 319 N 0,7222
8 123 S 0,278

Eu ainda precisaria usar count(*) na consulta?

query

2 respostas

  1. Best Answer
    create table #table2 (id int, choice char(1))

insert into #table2 values (1,'N')
go 55

insert into #table2 values (1,'Y')
go 17

insert into #table2 values (8,'N')
go 319

insert into #table2 values (8,'Y')
go 123

select id
 ,count(*) as "total"
 ,choice
from #table2
where id in (1,8)
group by id, choice


select t.id,t.total,t.choice, (1.*total/grandtotal) as [percent]
from (select id
 ,count(*) as "total"
 ,choice
from #table2
where id in (1,8)
group by id, choice
) as t
join (
select id
 ,count(*) as "grandtotal"
 from #table2
where id in (1,8)
group by id
) as g on t.id=g.id
    • 0

  2. Use uma janela sumsobre o agregadocount

    select id
 ,count(*) as "total"
 ,choice
 ,count(*) * 1.0 / sum(count(*)) over (partition by id) as "percent"
from table1
where id in (1,8)
group by
  id,
  choice;
    • 0

relate perguntas