多条件交易记录统计及仅成功交易频次的SQL查询需求

Hey there! Let's break down how to add the successful transaction frequency count to your existing query.

First, let's recap your original query: it's doing a left join between transaction_names and transaction_report_data, filtering transactions within a specific date range, then grouping by transaction key, description, and mode to get the total transaction frequency.

To include successful transaction counts, we just need to add a conditional count based on how your table defines a "successful" transaction. I'll assume there's a field like status in transaction_report_data where a value like 'success' indicates a successful transaction—adjust the condition if your setup uses something different (like is_success = 1).

Option 1: Show both total and successful frequencies in one result #

This lets you see both metrics side by side for each group:

select 
    tt.transaction_key, 
    tt.description, 
    t.mode, 
    count(t.TransactionType) as TotalFrequency,
    -- Count only rows where the transaction was successful
    count(case when t.status = 'success' then 1 end) as SuccessFrequency
from transaction_names tt 
left join transaction_report_data t 
    on tt.transaction_key = t.TransactionType 
    and t.created >= '2017-04-11' 
    and t.created <= '2018-04-13' 
group by tt.transaction_key, tt.description, t.mode;

The CASE statement returns 1 only for successful transactions, and COUNT ignores NULL values, so it effectively counts just the successful ones.

Option 2: Only count successful transactions #

If you want a result set focused solely on successful transaction frequencies, you can filter directly in the join or where clause:

Left join version (includes all transaction names, even those with 0 successful transactions):

select 
    tt.transaction_key, 
    tt.description, 
    t.mode, 
    count(t.TransactionType) as SuccessFrequency
from transaction_names tt 
left join transaction_report_data t 
    on tt.transaction_key = t.TransactionType 
    and t.created >= '2017-04-11' 
    and t.created <= '2018-04-13'
    and t.status = 'success' -- Filter for success here
group by tt.transaction_key, tt.description, t.mode;

Inner join version (only shows transaction names that have at least one successful transaction):

select 
    tt.transaction_key, 
    tt.description, 
    t.mode, 
    count(t.TransactionType) as SuccessFrequency
from transaction_names tt 
join transaction_report_data t 
    on tt.transaction_key = t.TransactionType 
    and t.created >= '2017-04-11' 
    and t.created <= '2018-04-13'
where t.status = 'success' -- Alternatively filter here
group by tt.transaction_key, tt.description, t.mode;

Just swap out t.status = 'success' with your actual success condition if your table uses a different field or value!

内容的提问来源于stack exchange，提问作者Talib