获取SQL中计数列的总和

Question

我有以下查询

SELECT 
    dtc.coupon_type_company_name,  
    COUNT(*) * dtc.coupon_type_company_coupon_amount AS 'Total_Coupon_To_Be_Used',
    dtc.coupon_type_company_coupon_months_combinable
FROM 
    [dbo].[coupon_type_Company_User] dtcu
JOIN 
    coupon_type_Company dtc ON dtcu.coupon_type_Company_ID = dtc.id
JOIN 
    person p ON dtcu.userID = p.userID
WHERE 
    coupon_type_company_coupon_is_combinable = 1 
    OR coupon_type_company_has_coupon = 1 
    AND dtc.companyID = 1081
    AND p.is_active = 1
GROUP BY
    dtc.coupon_type_company_name,dtc.coupon_type_company_coupon_amount,
    dtc.coupon_type_company_coupon_months_combinable

这将返回以下内容：

​

​

但是，我想要的只是一列和一行，它应该是中间列(count(*)*dtc.coupon_type_company_coupon_amount)的总和。

我如何才能做到这一点并防止在我的代码后端(C#)中这样做？

Answer 1

您可以像这样包装您的查询：

SELECT SUM(Total_Coupon_To_Be_Used) AS the_sum
FROM (
   your query
) s

Answer 2

使用“表表达式”，如下所示：

select sum(Total_Coupon_To_Be_Used) from (
  SELECT dtc.coupon_type_company_name,  
    count(*) * dtc.coupon_type_company_coupon_amount as 'Total_Coupon_To_Be_Used',
    dtc.coupon_type_company_coupon_months_combinable
  FROM [dbo].[coupon_type_Company_User] dtcu
  JOIN coupon_type_Company dtc ON dtcu.coupon_type_Company_ID = dtc.id
  JOIN person p ON dtcu.userID = p.userID
  WHERE coupon_type_company_coupon_is_combinable = 1 
     or coupon_type_company_has_coupon = 1 
    and dtc.companyID = 1081
    AND p.is_active = 1
  GROUP BY
    dtc.coupon_type_company_name,dtc.coupon_type_company_coupon_amount,
    dtc.coupon_type_company_coupon_months_combinable
  ) x