SQL What Percentage of payments this year were card vs bank

Question

I am looking to find the percentage of credit card payments vs bank payments, within a specific date range.

I am expecting to find the percentage of the processed credit card payments vs bank payments. I have filtered my list to show the processed credit card payments and bank payments made within the specified date range, but I need to figure out the subquery to find the amount of credit card payments vs bank payments within that filtered table.

What I have is:

SELECT payment_method, date(created_at), status
FROM production.payments
WHERE created_at >= ‘2023-01-01’
   and payment_method != ‘cash’
   and status != ‘failed’

This has given me the data I need to pull from (all credit card payments and bank payments made this year), but now I need to gather the count of all the credit card payments and the count of all the bank payments (separately)

Payment_method	Created_at
Card	2023-01-01
Card	2023-01-01
Card	2023-01-01
Bank	2023-01-01
Bank	2023-01-01
Card	2023-01-02
Bank	2023-01-03
Card	2023-01-03
Card	2023-01-03
Bank	2023-01-04
Card	2023-01-04
Card	2023-01-05

Desired output - the number of times each has been used this year (I can then figure out the percentage)

Payment_method	Count
Card	5,061
Bank	8,632

OR

Desired output - percentage of credit card payments vs bank payments

Payment_method	Percentage
Card	37%
Bank	63%

Answer 1

I wrap your query in 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]