sql - How avoid cartesian product when we sum (Two different column from dif. Tables) -

sql - How avoid cartesian product when we sum (Two different column from dif. Tables) -

sql engine: mssql

target: merge 2 different sum

this query:

select z.zlec_id zlec, ( case when sum(netto_blind_discout * p.count) not null sum( netto_blind_discout * p.count) else 0 end + case when sum(netto2 * d.count) not null sum(netto2 * d.count) else 0 end ) res, sum(netto_blind_discout * p.count), sum(netto2 * d.count) zetter z total outer bring together ( select netto_blind_discout, count, zlec_id position ) p on z.zlec_id = p.zlec_id total outer bring together ( select netto2, count, zlec_id d_additional ) d on z.zlec_id = d.zlec_id z.zlec_id = 123123 grouping z.zlec_id

how avoid cartesian product beetwen first bring together , second? it's generate me unusual result:

final result

(sum of product * number_of_rows in d_additional) + (sum of d_additional * number_of_rows in product), (sum of product * number_of_rows in d_additional), (sum of d_additional * number_of_rows in product)

where fault?

solution:

(move grouping sum root child)

select z.zlec_id zlec, ( case when sum_p not null sum_p else 0 end + case when sum_d not null sum_d else 0 end ) res, sum_p, sum_d zetter z, ( select sum(netto_blind_discout * count) sum_p, p.zlec_id zetter z left bring together position p on z.zlec_id = p.zlec_id grouping p.zlec_id ) p, ( select sum(netto2 * count) sum_d, d.zlec_id zetter z left bring together d_additional d on z.zlec_id = d.zlec_id grouping d.zlec_id ) d z.zlec_id = 123123 , d.zlec_id = z.zlec_id , p.zlec_id = z.zlec_id

sql sql-server cartesian-product