SUM of two or more SUB-SELECT

Transact-SQL
1

Hi,

I would like to sum two or more sub-select queries based on the same table.

Like this

SELECT
(
(SELECT AMOUNT FROM GLOBAL AS SUBGLOBAL WHERE SUBGLOBAL.KEY = GLOBAL.KEY AND DOC = INVOICE)
+
(SELECT AMOUNT FROM GLOBAL AS SUBGLOBAL WHERE SUBGLOBAL.KEY = GLOBAL.KEY AND DOC = CREDITNOTE)
) AS BALANCE
FROM GLOBAL

Here i get a null value

I have tried to SUM ((SELECT ...) + (SELECT ...) FROM ... but i get message cannot aggregate subquery

Your help is appreciated in advance :slight_smile:
Cheers
Didier

2

Try this
SELECT SUM(SUBGLOBAL) as sumSubglobal FROM
(
SELECT AMOUNT FROM GLOBAL AS SUBGLOBAL WHERE SUBGLOBAL.KEY = GLOBAL.KEY AND DOC = INVOICE
UNION ALL
SELECT AMOUNT FROM GLOBAL AS SUBGLOBAL WHERE SUBGLOBAL.KEY = GLOBAL.KEY AND DOC = CREDITNOTE
) AS TBL

3

I think you want a total by KEY? If not, you can remove KEY and GROUP BY and just total everything into one amount.

SELECT [KEY], SUM(AMOUNT) AS SUM_AMOUNT
FROM GLOBAL
WHERE DOC = INVOICE OR DOC = CREDITNOTE
GROUP BY [KEY]