MSSQL: Get Subtotal of a total

How can I write a query to get the subtotal of a total? Example is below.

select
task_id,
client_id,
SUM(value1 + value2) AS 'Total 1',
SUM(value3 + value4) AS 'Total 2',
--pseudocode
SUM(Total 1 + Total 2) AS 'Total 1 + Total 2'
from table1
GROUP BY task_id,client_id

Expected Results:

  • How can I have multiple common table expressions in a single SELECT statement?
  • Real vs. Floating Point vs. Money
  • SQL Server date conversion not is formatting the output
  • Where is the SQL Query Analyzer in SQL Server Management Studio 2012
  • What are the problems with a join between two tables in two different databases?
  • Create a join that can (include some) or (include all except some) records
  • task_id | client_id | Total 1 | Total 2 | Total 1 + Total 2 |
       1         4          2          4            6
    

    2 Solutions collect form web for “MSSQL: Get Subtotal of a total”

    It’s can either wrap it in a subquery:

    SELECT 
        task_id
        , client_id
        , [Total 1]
        , [Total 2]
        , SUM([Total 1], [Total 2]) AS 'Total 1 + Total 2'
    FROM
    (
      select
        task_id,
        client_id,
        SUM(value1 + value2) AS 'Total 1',
        SUM(value3 + value4) AS 'Total 2',
        --pseudocode
        SUM(Total 1 + Total 2) AS 'Total 1 + Total 2'
      from table1
      GROUP BY task_id,client_id
    ) a 
    GROUP BY task_id, client_id
    

    Or, instead of using SUM, you could simply add the original values:

    select
    task_id,
    client_id,
    SUM(value1 + value2) AS 'Total 1',
    SUM(value3 + value4) AS 'Total 2',
    --pseudocode
    value1 + value2 + value3 + value4 AS 'Total 1 + Total 2'
    from table1
    GROUP BY task_id,client_id
    

    You cannot re-use column aliases in the same select. Instead, just repeat the operation:

    select task_id, client_id,
           SUM(value1 + value2) AS [Total 1],
           SUM(value3 + value4) AS [Total 2],
           SUM(value1 + value2 + value3 + value4) AS [Total 1 + Total 2]
    from table1
    group by task_id, client_id;
    

    If repeating the operation is cumbersome, then you can consider a subquery or CTE.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.