CODEWITHSUNDEEP
sqlsql-serverjoinduplicatessubquery
RAZ
RAZ

Reputation: 61

How to sum values of two matched tables without getting duplicates in SQL

enter image description here

I got above result in Excel after filtering two tables by (where) tableA.cost = TableB.total.

I tried to apply the same in SQL, but I got duplicate values.

Here is my SQL query:

SELECT sum(total), sum(cost)
FROM TableA
INNER JOIN TableB ON tableA.ID = tableB.ClientID
WHERE tableA.ID = 45454
  AND TableA.code= 'A'
  AND TableA.period = 1
  AND TableB.status = 71
  AND TableA.SORT = 'SS'
  AND TableB.SORT= 'SS'

and the result was 

total   cost
-235.80 -47.16

So the total column was duplicated five times based on the row numbers in cost column.

Is there any way to prevent total column being not duplicate ?

Upvotes: 1

Views: 1597

Answers (1)

GMB
GMB

Reputation: 222542

You could sove this specific requirement with just two subqueries:

select
    (
        select sum(total)
        from tableb
        where clientID = 45454 and status = 71 and sort = 'SS'
    ) sum_total,
    (
        select sum(cost)
        from tablea
        where id = 45454 and code= 'A' and period = 1 and sort = 'ss'
    ) sum_cost

If you need to process more (or all) ids at once, then you could also join two aggregate subqueries:

select a.id, b.sum_total, a.sum_cost
from (
    select clientID, sum(cost) sum_cost
    from tableb
    where status = 71 and sort = 'SS'
) b
inner join (
    select id, sum(total) sum_total
    from tablea
    where code= 'A' and period = 1 and sort = 'ss'
    group by id
) a on a.id = b.clientID
group by a.id

If you always have just one record in tablea per id, then queries get simpler:

select 
    (
        select sum(b.total)
        from tableb b
        where b.clientID = a.id and b.status = 71 and b.sort = 'SS'
    ) sum_total,
    cost
from tablea
where code= 'A' and period = 1 and sort = 'ss' -- and id = 45454

Upvotes: 1

Related Questions