CODEWITHSUNDEEP
sqlt-sqljoincountsum
user3127554
user3127554

Reputation: 577

Get the sum of a count column in SQL

I have the following query 

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

This returns the following:

enter image description here

What I want to have however is just one column and one row that should take the SUM of my middle column (count(*)*dtc.coupon_type_company_coupon_amount).

How could I achieve this and prevent doing this in my code backend (C#)

Upvotes: 4

Views: 10252

Answers (2)

The Impaler
The Impaler

Reputation: 48770

Use a "Table Expression", as in:

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

Upvotes: 2

Eric
Eric

Reputation: 3257

You can wrap your query like this:

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

Upvotes: 5

Related Questions