CODEWITHSUNDEEP
sqlsql-server
Sai
Sai

Reputation: 221

SQL Server Query to compare 2 select statements data

Can you please help me in combining the below 2 queries. The result should be as shown in attached image.

I would like to compare the balance between 2 queries and flag the balance when state balance is less than city balance.

 select CT_ID,  sum(balance) as StateBalance
from tblApp
where StateJurisdiction='New york'
group by CT_ID
order by CT_ID

select CT_ID,  sum(balance) as CityBalance
from tblApp
where StateJurisdiction in ('BRONX','BROOKLYN')
group by CT_ID
order by CT_ID

Upvotes: 1

Views: 77

Answers (2)

Alex
Alex

Reputation: 5157

You can do a simple INNER JOIN between two queries:

SELECT a.CT_ID, StateBalance, CityBalance,
    ( CASE WHEN StateBalance < CityBalance THEN 'Yes' ELSE 'No' END ) AS Flag
FROM
    ( select CT_ID,  sum(balance) as StateBalance
    from tblApp
    where StateJurisdiction='New york'
    group by CT_ID
    order by CT_ID ) AS a
INNER JOIN
    ( select CT_ID, sum(balance) as CityBalance
    from tblApp
    where StateJurisdiction in ('BRONX','BROOKLYN')
    group by CT_ID
    order by CT_ID ) AS b
ON a.CT_ID = b.CT_ID

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use conditional aggregation:

SELECT
    CT_ID,
    StateBalance = SUM(CASE WHEN StateJurisdiction = 'New York' THEN balance ELSE 0 END),
    CityBalance  = SUM(CASE WHEN StateJurisdiction IN('BRONX','BROOKLYN') THEN balance ELSE 0 END),
    Flag =
        CASE
            WHEN
                SUM(CASE WHEN StateJurisdiction = 'New York' THEN balance ELSE 0 END) <
                SUM(CASE WHEN StateJurisdiction IN('BRONX','BROOKLYN') THEN balance ELSE 0 END)
            THEN 'Yes'
            ELSE
                ''
        END
FROM tblApp
WHERE 
    StateJurisdiction IN('BRONX','BROOKLYN', 'New York')
GROUP BY CT_ID
ORDER BY CT_ID

Upvotes: 2

Related Questions