これは私が作成したクエリです。このクエリは動作しますが、請求書と貸方という2つの結果が得られます。私は1つの結果、つまり合計が欲しいだけです。
SELECT
sum ([linetotal])
FROM
(
SELECT
sum(t1.[LineTotal]) AS 'Linetotal'
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
WHERE t0.taxdate >= '20170101' and t0.taxdate <= '20170131' and t0.[canceled] ='n' and T0.[SlpCODE] = '4'
union all
SELECT
sum(-T1.[LineTotal] ) AS 'Linetotal'
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
WHERE t0.taxdate >= '20170101' and t0.taxdate <= '20170131' and t0.[canceled] ='n' and T0.[SlpCODE] = '4'
)
as a
group by a.linetotal
group byを削除する必要があります。
SELECT
sum ([linetotal])
FROM
(
SELECT
sum(t1.[LineTotal]) AS 'Linetotal'
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
WHERE t0.taxdate >= '20170101' and t0.taxdate <= '20170131' and t0.[canceled] ='n' and T0.[SlpCODE] = '4'
union all
SELECT
sum(-T1.[LineTotal] ) AS 'Linetotal'
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
WHERE t0.taxdate >= '20170101' and t0.taxdate <= '20170131' and t0.[canceled] ='n' and T0.[SlpCODE] = '4'
)
as a