Select from multiple tables and make difference

Question

Line 2 or Line 3 alone working

I need to create column from multiple tables and sum...

Some help?

select naziv as 'Naziv artikla',
SUM (cast(kolicina as float)) as 'Nabavka' from (select naziv, kolicina from ulazni_racun_roba_roba union all select naziv, kolicina from mp_ulazni_racun_ostalo_roba) as alltables
SUM (cast(kolicina as float)) as 'Prodaja' from (select roba, kolicina from mp_racun_roba union all select roba, kolicina from mp_faktura_roba) as alltables
group by naziv

I need result column:

1) Naziv artikla

2) Nabavka

3) Prodaja

4) Difference (Nabavka-Prodaja)

Jayesh Vaghasiya · Answer

declare @ulazni_racun_roba_roba table (naziv varchar(32),kolicina varchar(32)) declare @mp_ulazni_racun_ostalo_roba table (naziv varchar(32),kolicina varchar(32)) declare @mp_racun_roba table (roba varchar(32),kolicina varchar(32)) declare @mp_faktura_roba table (roba varchar(32),kolicina varchar(32)) --insert data in all table and then try select naziv AS 'Naziv artikla',sum(Nabavka) As 'Nabavka',sum(Prodaja) As 'Prodaja', (sum(Nabavka)-sum(Prodaja)) As 'Difference' from ( select naziv , SUM (cast(kolicina as float)) as Nabavka,SUM (cast(kolicina as float)) as Prodaja from ( select naziv, kolicina from @ulazni_racun_roba_roba union all select naziv, kolicina from @mp_ulazni_racun_ostalo_roba ) as alltables group by naziv union all select roba as naziv, SUM (cast(kolicina as float)) as Nabavka ,SUM (cast(kolicina as float)) as Prodaja from ( --i think roba is different collumn but i use (roba and naziv is same collumn and both are pk) select roba , kolicina from @mp_racun_roba union all select roba , kolicina from @mp_faktura_roba ) as alltables group by roba ) final group by naziv

Nitin Sontakke · Answer

Goran, Please read carefully and answer questions properly. You seem to be selecting sums from two completely different tables. Is there any relationship between these two tables? If yes, what is the joining key? If there is relationship then only it can be done. No relationship, cannot be done. This is logical. Please give sample 3-4 rows from each table and show us if they related then how.

Nitin Sontakke · Answer

As I keep saying to everybody, please BREAK the problem in pieces. DO NOT swollow the big chunks.

Goran Bibic · Answer

Example