CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012sql-server-2008-r2
IeeTeY
IeeTeY

Reputation: 93

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:

task_id | client_id | Total 1 | Total 2 | Total 1 + Total 2 |
   1         4          2          4            6

Upvotes: 1

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 1

SchmitzIT
SchmitzIT

Reputation: 9552

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

Upvotes: 1

Related Questions