SQL按月统计充值金额总和 查询改写求助

Hey there! Let's tweak your query to get the monthly total of Top_Up_Value you're aiming for. Here's the revised SQL that will give you exactly the output format you want:

SELECT 
    DATENAME(Month, TOPUP.tu_timestamp) AS MonthName,
    CAST(ROUND(SUM(ISNULL(TOPUP.tu_credit - NC.initial_bal, TOPUP.tu_credit) / TOPUP.currency_rate), 2) AS decimal(18, 2)) AS Top_Up_Value
FROM dbfastshosted.dbo.fh_mf_top_up_logs AS TOPUP 
INNER JOIN dbo.cdf_terminal_user AS TU ON TOPUP.terminal_user_id = TU.terminal_user_id 
INNER JOIN dbo.cdf_currency AS CR ON TOPUP.currency_id = CR.currency_id 
INNER JOIN dbo.cdf_cuid AS CU ON TOPUP.cu_id = CU.cu_id 
INNER JOIN dbo.cdf_card_role AS CO ON CO.id = CU.card_role_id 
INNER JOIN dbo.cdf_terminal_user_account AS UA ON UA.terminal_user_id = TU.terminal_user_id 
INNER JOIN dbo.cdf_terminal AS TM ON TM.terminal_id = UA.terminal_id 
INNER JOIN dbfastshosted.dbo.fh_sales_map AS MA ON MA.tu_log_id = TOPUP.tu_log_id 
LEFT OUTER JOIN dbfastshosted.dbo.fh_mf_new_card_logs AS NC ON MA.nc_log_id = NC.nc_log_id 
WHERE 
    ISNULL(TOPUP.tu_credit - NC.initial_bal, TOPUP.tu_credit) > 0
    AND YEAR(TOPUP.tu_timestamp) = '2017'
GROUP BY 
    DATENAME(Month, TOPUP.tu_timestamp),
    DATEPART(Month, TOPUP.tu_timestamp)
ORDER BY 
    DATEPART(Month, TOPUP.tu_timestamp);

Key Changes I Made: #

• Added SUM() aggregation: Wrapped your original Top_Up_Value calculation inside SUM() to calculate the total for each month instead of returning individual transaction records.
• Simplified the GROUP BY clause: Removed all individual transaction/terminal fields (like tu_log_id, terminal_name) and grouped only by the month name and month number. The month number ensures your results sort correctly from January to December (instead of alphabetical order, which would mix up months like "April" and "August").
• Adjusted filters: Removed month(TOPUP.tu_timestamp) = 1 (which was limiting results to only January) and TM.terminal_id = 7 (which was restricting to a single terminal) since you want totals across all terminals for the full year.
• Added proper ordering: The ORDER BY DATEPART(Month, TOPUP.tu_timestamp) line guarantees your months appear in chronological order, not just alphabetical.

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