Adding multiple select statements without using Union ALL-CodePudding

I currently wrote a query that adds multiple select statements using UNION ALL and returns the sum of the amounts within each select statement. My question: is there any way or approach to make this query smaller (not that lengthy)?

The only thing that changes within each subquery is the ID# used to grab ID1 from the Accnt Table

 SELECT SUM(TOTAL) TOTAL
 FROM
(
SELECT ISNULL (round(sum(signeddata),2),0)
FROM BUDGET
WHERE CATEGORY = 'ACTL'
AND TIME BETWEEN '20210100' AND '20210800'
AND CURRENCY = 'DOL'
AND DATASRC IN (
    SELECT ID1 FROM DataSrc
    WHERE ID7 = 'Logic')
AND ACCOUNT IN (
    select ID1 from Accnt
    where ID7 = 'EXPENSES')
AND ENTITY IN (
    SELECT ID1 FROM Entity
    where ID9 = 'H2')

UNION ALL

SELECT ISNULL (round(sum(signeddata),2),0)*-1
FROM BUDGET
WHERE CATEGORY = 'ACTL'
AND TIME BETWEEN '20210100' AND '20210800'
AND CURRENCY = 'DOL'
AND DATASRC IN (
    SELECT ID1 FROM DataSrc
    WHERE ID7 = 'Logic')
AND ACCOUNT IN (
    select ID1 from Accnt
    where ID7 = 'MERC')
AND ENTITY IN (
    SELECT ID1 FROM Entity
    where ID9 = 'H2')

    UNION ALL

SELECT ISNULL (round(sum(signeddata),2),0)*-1
FROM BUDGET
WHERE CATEGORY = 'ACTL'
AND TIME BETWEEN '20210100' AND '20210800'
AND CURRENCY = 'DOL'
AND DATASRC IN (
    SELECT ID1 FROM DataSrc
    WHERE ID7 = 'Logic')
AND ACCOUNT IN (
    select ID1 from Accnt
    where ID11 = 'SYNP')
AND ENTITY IN (
    SELECT ID1 FROM Entity
    where ID9 = 'H2')
) S

CodePudding user response：

One idea is to sum case-semijoins instead of putting them in the FROM clause:

SELECT 
    ISNULL(round(sum(signeddata),2),0)
    *
    (
        case when ACCOUNT IN (select ID1 from Accnt where ID7 = 'EXPENSES') 
        then 1 else 0 end * 1
         
        case when ACCOUNT IN (select ID1 from Accnt where ID7 = 'MERC') 
        then 1 else 0 end * (-1)
         
        case when ACCOUNT IN (select ID1 from Accnt where ID7 = 'SYNP') 
        then 1 else 0 end * (-1)
    )
FROM BUDGET
WHERE CATEGORY = 'ACTL'
AND TIME BETWEEN '20210100' AND '20210800'
AND CURRENCY = 'DOL'
AND DATASRC IN (
    SELECT ID1 FROM DataSrc
    WHERE ID7 = 'Logic')
AND ENTITY IN (
    SELECT ID1 FROM Entity
    where ID9 = 'H2')

CodePudding user response：

You can just use IN for the ID7 values

Always specify table references for columns, especially on correlated subqueries

SELECT
  ISNULL(round(sum(b.signeddata), 2), 0) TOTAL
FROM BUDGET b
WHERE b.CATEGORY = 'ACTL'
  AND b.TIME BETWEEN '20210100' AND '20210800'
  AND b.CURRENCY = 'DOL'
  AND b.DATASRC IN (
      SELECT ds.ID1 FROM DataSrc ds
      WHERE ds.ID7 = 'Logic')
  AND b.ACCOUNT IN (
      select a.ID1 from Accnt a
      where a.ID7 IN ('EXPENSES', 'MERC', 'SYNP'))
  AND b.ENTITY IN (
      SELECT e.ID1 FROM Entity e
      where e.ID9 = 'H2');

Note the rounding will be slightly different if you do it like this