Comparing aggregated columns to non aggregated columns to remove matches-CodePudding

I have two separate tables from two different databases that are performing a matching check.

If the values match I want them out of the result set. The first table (A) has multiple entries that contain the same symbol matches for the matching columns in the second table (B). The entries in table B, if added up will ideally equal the value of one of the matching rows of A.

The tables look like below when queried separately.

Underneath the tables is what my query currently looks like. I thought if I group the columns by the symbols I could use the SUM of B to add up to the value of A which would get rid of the entries. However, I think because I am summing from B and not from A, then the A doesn't count as an aggregated column so must be included in the group by and doesn't allow for the summing to work in the way I'm wanting it to calculate.

How would I be able to run this query so the values in B are all summed up. Then, if matching to the symbol/value from any of the entries in A, don't get included in the result set?

Table A

| Symbol | Value |
|--------|-------|
| A      |  1000 |
| A      |  1000 |
| B      |  1440 |
| B      |  1440 |
| C      |  1235 |

Table B

| Symbol | Value |
|--------|-------|
| A      |  750 |
| A      |  250 |
| B      |  24  |
| B      |  1416|
| C      |  1874  |

SELECT DBA.A, DBB.B
      FROM DatabaseA DBA
      INNER JOIN DatabaseB DBB on DBA.Symbol = DBB.Symbol
      and DBA.Value != DBB.Value
      group by DBA.Symbol, DBB.Symbol, DBB.Value
      having SUM(DBB.Value) != DBA.Value
    order by Symbol, Value

Edited to add ideal results

Table C

| SymbolB | ValueB | SymbolA | ValueA |
|--------|-------|---------|--------|
| C      |  1874 |   C     | 1235   |

Wherever B adds up to A remove both. If they don't add, leave number inside result set

CodePudding user response：

I will use CTE and use this common table expression (CTE) to search in Table A. Then join table A and table B on symbol.

WITH tDBB as (
SELECT DBB.Symbol, SUM(DBB.Value) as total
FROM   tableB as DBB
GROUP BY DBB.Symbol
  )
SELECT distinct DBB.Symbol as SymbolB, DBB.Value as ValueB, DBA.Symbol as SymbolA, DBA.Value as ValueA
FROM tableA as DBA 
INNER JOIN tableB as DBB on DBA.Symbol = DBB.Symbol
WHERE DBA.Symbol in (Select Symbol from tDBB)
AND NOT DBA.Value in (Select total from tDBB)

Result:

SymbolB ValueB  SymbolA ValueA
C       1874    C       1235

CodePudding user response：

with t3 as (
            select   symbol
                    ,sum(value) as value
            from     t2
            group by symbol
           )
select     *
from       t3 join t on t.symbol = t3.symbol and t.value != t3.value

symbol	value	Symbol	Value
C	1874	C	1235

Fiddle