CODEWITHSUNDEEP
sqlsql-server
Vitalii
Vitalii

Reputation: 11071

MSSQL: How to add a column with a sum of three others

I have a table with three columns

SELECT 
  (SELECT COUNT(*) FROM Table1 WHERE IDU = 406536063) as 'col1',
  (SELECT COUNT(*) FROM Table2 WHERE CustomerNr = 406536063) as 'col2',
  (SELECT COUNT(*) FROM Table3 WHERE CustomerNr = 406536063) as 'col3'

How can I add in MSSQL the forth column with the sum of values in col1, col2, col3?

Upvotes: 0

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can also move the queries to the FROM clause:

SELECT col1, col2, col3, (col1 + col2 + col3)
FROM (SELECT COUNT(*) as col1 FROM Table1 WHERE IDU = 406536063) t1 CROSS JOIN
     (SELECT COUNT(*) as col2 FROM Table2 WHERE CustomerNr = 406536063) t2 CROSS JOIN
     (SELECT COUNT(*) as col3 FROM Table3 WHERE CustomerNr = 406536063) t3;

Although I do use subqueries in the SELECT, I usually put them in the FROM clause.

Upvotes: 4

Eray Balkanli
Eray Balkanli

Reputation: 7960

Using cte might help:

;with cte (col1,col2,col3) as
(
   SELECT 
     (SELECT COUNT(*) FROM Table1 WHERE IDU = 406536063) as 'col1',
     (SELECT COUNT(*) FROM Table2 WHERE CustomerNr = 406536063) as 'col2',
     (SELECT COUNT(*) FROM Table3 WHERE CustomerNr = 406536063) as 'col3'
)
select col1+col2+col3
from cte

Upvotes: 2

Related Questions