Need the sum of two columns from subquery-CodePudding

I have here my query and subquery to generate totals for the types I'm looking for in my database but now I need to somehow get the total for the entire columns. I feel like the solution is right in front of me but I cannot figure it out. Any step in the right direction would be greatly appreciated.

SELECT
  SUM(b.aGiven) AS given,
  SUM(b.aUsed) AS used
FROM  UserAccountGroups AS uag
LEFT OUTER JOIN (
    SELECT
      uac1.UserAccountGroupID AS aGroupID,
      SUM(ua.UserAccountUsedAmount) AS aUsed,
      0 AS aGiven
    FROM UserAccounts AS ua
    LEFT OUTER JOIN UserAccountCodes AS uac1 ON ua.UserAccountCode = uac1.UserAccountCode
    WHERE uac1.UserAccountCodeCreatedOn between '07-11-2020' and '07-11-2021'
    GROUP BY uac1.UserAccountGroupID

    UNION

    SELECT
      uac.UserAccountGroupID AS aGroupID,
      0 AS aUsed,
      SUM(uac.UserAccountCodeAmount) AS aGiven
    FROM UserAccountCodes AS uac
    LEFT OUTER JOIN UserAccounts AS ua1 ON uac.UserAccountCode = ua1.UserAccountCode
    WHERE uac.UserAccountCodeCreatedOn between '07-11-2010' and '07-11-2021'
    GROUP BY uac.UserAccountGroupID
) AS b ON b.aGroupID = uag.UserAccountGroupID
GROUP BY uag.ReportGroup

This is a snippet of the result of my query and the two columns I need added up

*** Update *** I'm sorry if my question was unclear. This is a query I am using to pull the totals for each type of 'ReportGroup' from the db. Now, rather than needing the totals per group, I need the totals per the column. The idea is to pass in date variables in my codebehind to pull from custom dates and now I would like to have a grand total per column at the bottom of my report. I know I don't need to select any data from UserAccountGroups but I'm having trouble re-working the query to get accurate results. A point in the right direction would be very helpful and thank you beforehand.

CodePudding user response：

You need to take out UserAccountGroupID in order to get the Sum of all. Right now, you have UserAccountGroupID in, so it is summing by UserAccountGroupID. Hope this answers your question.

CodePudding user response：

If you can provide DML and DDL statements this could have tested easily. This will give sum for each aGroupID and grand total for columns aUsed, aGiven.

SELECT * 

into #temp_agg_data
FROM
(
 SELECT
   uac1.UserAccountGroupID AS aGroupID,
   SUM(ua.UserAccountUsedAmount) AS aUsed,
   0 AS aGiven
 FROM UserAccounts AS ua
  LEFT OUTER JOIN UserAccountCodes AS uac1 ON ua.UserAccountCode = uac1.UserAccountCode
 WHERE uac1.UserAccountCodeCreatedOn between '07-11-2020' and '07-11-2021'
 GROUP BY uac1.UserAccountGroupID

 UNION ALL

  SELECT
    uac.UserAccountGroupID AS aGroupID,
    0 AS aUsed,
    SUM(uac.UserAccountCodeAmount) AS aGiven
  FROM UserAccountCodes AS uac
   LEFT OUTER JOIN UserAccounts AS ua1 ON uac.UserAccountCode = ua1.UserAccountCode
  WHERE uac.UserAccountCodeCreatedOn between '07-11-2010' and '07-11-2021'
   GROUP BY uac.UserAccountGroupID

 ) AS A

--Last select will list down all records with sum group by [aGroupID] and grand total for  [aUsed] ,  [aGiven] as a row below as 'Grand Total'
SELECT 
  [aGroupID]
  ,[aUsed]
  ,[aGiven]
FROM
 #temp_agg_data

 UNION ALL

  SELECT 
  'Grand Total' AS  [aGroupID]
  , SUM(aUsed) as [aUsed]
  , SUM(aUsed) as [aGiven]
 FROM  #temp_agg_data

 DROP TABLE #temp_agg_data