SQL今年的付款比例是卡还是银行

我用cte包装你的查询。

WITH cte AS (
    SELECT payment_method, date(created_at), status
    FROM production.payments
    WHERE created_at >= ‘2023-01-01’
       and payment_method != ‘cash’
       and status != ‘failed’
)
SELECT DISTINCT payment_method
, 100.00 * COUNT(*) OVER(PARTITION BY payment_method) / COUNT(*) OVER() AS [Percentage]

你可以计算每组,然后除以总数。。。。

create table payments (
  payment_method varchar(10), 
  created_at date
  );
  
insert into payments values 
('card', '2023-01-01'),
('card', '2023-01-01'),
('card', '2023-01-01'),
('card', '2023-01-01'),
('card', '2023-01-01'),
('card', '2023-01-01'),
('card', '2023-01-01'),
('bank', '2023-01-01'),
('bank', '2023-01-01');

select payment_method, 
 round(count(*) / sum(count(*)) over () * 100 ,2) as pct_of_total
from payments
--where your criteria
group by payment_method;

View on DB Fiddle