在一个表中按月获取多个值的总和
在一个表中按月获取多个值的总和
提问于 2018-01-01 10:09:59
我有带查询的表：
SELECT DATENAME(Month,TOPUP.tu_timestamp) AS MonthName, TM.terminal_name,
CAST(ROUND(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'
AND month(TOPUP.tu_timestamp) = 1
AND TM.terminal_id = 7
GROUP BY TOPUP.tu_log_id,DATENAME(Month,TOPUP.tu_timestamp),
TM.terminal_name,
TOPUP.tu_credit, NC.initial_bal, TOPUP.currency_rate, CU.card_type_id;
MonthName Terminal name Top Up Value
------------------------------------------------------
January Terminal 1 100
January Terminal 1 200
January Terminal 3 150
Feb Terminal 1 250
Feb Terminal 1 160
March Terminal 2 120
March Terminal 3 100我希望根据如下所示的月份获得充值的总金额：
MonthName Top Up Value
-----------------------------------
January 450
February 410
March 220
-----
Dec因为我是sql的初学者，我不知道怎么做。在这些方面真的需要帮助。谢谢!
回答 3
Stack Overflow用户
回答已采纳
发布于 2018-01-01 17:12:25
SELECT DATENAME(Month,TOPUP.tu_timestamp) AS MonthName,
SUM(CAST(ROUND(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 Stack Overflow用户
发布于 2018-01-01 10:51:53
查看group by语句，您发布的结果显示的列数少于查询中的列数。调整一下，你就会得到你的结果
Stack Overflow用户
发布于 2018-01-01 10:54:06
尝尝这个
SELECT DATENAME(Month,TOPUP.tu_timestamp) AS MonthName,
SUM(CAST(ROUND(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'
AND month(TOPUP.tu_timestamp) = 1
AND TM.terminal_id = 7
GROUP BY DATENAME(Month,TOPUP.tu_timestamp)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48046773
复制相关文章
相似问题
腾讯云开发者
